Literals
Temporal literals are used to specify hard-coded values in SQL-statements or in parameter values. The examples below give you the basic idea on the basic format of these literals. The examples use the date March 18th 2001 and the time 23:58.
DATE'2001-03-18'
TIME'23:58:00'
TIMESTAMP'2001-03-18 23:58:00.00045'
When I want to insert the above literals, I can use the following statements:
INSERT INTO my_date_table VALUES (DATE'2001-03-18')
INSERT INTO my_time_table VALUES (TIME'23:58:00')
INSERT INTO my_timestamp_table VALUES (TIMESTAMP'2001-03-18 23:58:00.00045')
Intervals are more complex, but the examples below might give you the basic idea. See the Mimer SQL Reference manual for more details.
| INTERVAL'30'YEAR | (will denote the time span of 30 years) |
| INTERVAL'15'MINUTE | (15 minutes) |
| INTERVAL'3:02.3'MINUTE TO SECOND | (3 minutes 2,3 seconds) |
Arithmetic
Datetime arithmetic is mainly about three things:
1. Calculating an interval between two dates, times or timestamps.
2. Adding an interval to a date, time or timestamp to compute another date, time or timestamp.
3. Doing addition, subtraction, multiplication and division on intervals.
The next examples use to following table:
CREATE TABLE my_age_table (
name CHAR(20),
birth_date DATE)
The example below shows a simple SQL-statement which selects a table containing birth dates and names on a condition that the person in question should be at least 30 years old.
SELECT name,birth_date
FROM my_age_table
WHERE (current_date-birth_date)YEAR >= INTERVAL'30'YEAR
The next example shows how to construct an SQL statement which will give a result set with names and dates when the persons in the table become adults (18 years old).
SELECT name,birth_date+INTERVAL'18'YEAR
FROM my_age_table
The third example will give you the list of persons who are at least twice as old as ‘Robert’ is.
SELECT a.name,a.birth_date
FROM my_age_table a,my_age_table b
WHERE (CURRENT_DATE-a.birth_date)YEAR>=2*(CURRENT_DATE-b.birth_date)YEAR
AND b.name='Robert'
Temporal Data in Parameters
Standardized literals offer a way of specifying temporal data in your SQL-statements. But what about parameters values? The parameter data is often extracted directly from external sources, such as a text file or a user dialog.
The C-program below for Microsoft Windows ODBC gives a few simple examples of entering temporal data in parameters. For simplicity, all error handling has been omitted. If you want to run the sample, you need to have created the my_date_table described earlier in this document.
#include <windows.h>
#include <sqlext.h>
#include <string.h>
int main(int argc,char *argv[])
{
SQLHANDLE henv;
SQLHANDLE hdbc;
SQLHANDLE hstmt;
char param[100];
SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);
SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,0);
SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);
SQLConnect(hdbc,"investdb",SQL_NTS,"SYSADM",SQL_NTS,"SYSADM",SQL_NTS);
SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);
SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_DATE,0,0,
param,sizeof(param),NULL);
SQLPrepare(hstmt,"INSERT INTO my_date_table VALUES (?)",SQL_NTS);
strcpy(param,"2000-12-24");
SQLExecute(hstmt);
strcpy(param,"date'2001-12-24'");
SQLExecute(hstmt);
strcpy(param,"timestamp'2002-01-24 01:02:03'");
SQLExecute(hstmt);
strcpy(param,"{d'2002-12-24'}");
SQLExecute(hstmt);
strcpy(param,"{ts'2002-12-24 04:05:06'}");
SQLExecute(hstmt);
SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
SQLFreeHandle(SQL_HANDLE_ENV,henv);
}
ODBC and JDBC Escape Clause Literals
Since databases in general implement temporal data types very differently and as of today, few have adopted the standard, database clients offer a method of entering portable temporal data literals. The idea is that driver vendors are supposed to parse the SQL-string and convert escape clauses to something understood by the database engine.
The following examples list the same data as exemplified in the section ‘Literals’ above:
{d'2001-03-18'}
{t'23:58:00'}
{ts'2001-03-18 23:58:00.00045'}
INSERT INTO my_date_table VALUES ({d'2001-03-18'})
INSERT INTO my_time_table VALUES ({t'23:58:00'})
INSERT INTO my_timestamp_table VALUES ({ts'2001-03-18 23:58:00.00045'})
{interval'30'year}
{interval'15'minute}
{interval'3:02.3'minute to second}
If you use the escape clauses above when developing your application for another non-standardized RDBMS, they will work without modification when you switch to Mimer. It is assumed that the database is named DB, the user is USER and password is PASS. Change this to suit your environment.
Historic Calendars
A problem when representing historic dates is that history and cultures have used different calendars throughout the course of time. The major cause of inconsistency is leap days. Before most of the world agreed on the Gregorian calendar, countries and kingdoms more or less randomly decided to add dates to the calendar to compensate for missing leap days.
It is therefore impossible to accurately represent and do proper calculations on dates based on the calendar used by a specific culture. When defining the SQL-92 standard, the standards committee settled on the current Gregorian calendar from year 1 to 9999.
Mpm Home
Printer friendly
