Temporal Data Types and Mimer SQL

Introduction

Common problem when designing and implementing database applications are dates, times, timestamps and intervals. First of all, not all database vendors implement these features, and if they do they usually implement them differently.

This article describes the temporal data types that are defined in SQL standard and offers a unified standardized way of accessing these features.

Description

There are several features in Mimer SQL which help you to develop and deploy portable applications that use temporal data types. These features include:

• Fully standard SQL compatible date, time, timestamp and interval literals.
• Fully standard SQL compatible datetime and interval arithmetic.
• ODBC and JDBC interfaces support escape clause literals. These escape clauses are available in parameter values as well, as seen below.

Below, you can read descriptions of features and examples of their use. First we will start by defining what date, time, timestamps and intervals are.

Date

A date is a value denoting a specific day in history, or the future. The SQL standard defines dates to assume values denoting every day from year 1 to year 9999. The data type name is DATE, so the table definition to create a table with a single column containing a date is the following:

CREATE TABLE my_date_table (my_column DATE)

Time

A time is a value denoting a specific time (down to 1/1 000 000 000th of a second) within a date. The data type name is TIME[(p)]. The precision (number of fractional digits) is not mandatory, the default is 0.

For example, the table definition for a table with a column containing times with milliseconds is:

CREATE TABLE my_time_table (my_column TIME(3))

Timestamp

A timestamp is a value denoting both a date and a time. Like time, fractions down to 1/1 000 000 000th of a second may be represented. The data type name is TIMESTAMP[(n)], and the precision is not mandatory. The default precision is 6.

For example, the table definition to create a table with a column containing timestamps with microseconds is:

CREATE TABLE my_timestamp_table (my_column TIMESTAMP(6))

Intervals

An interval is a value denoting the time span between two dates, times, or timestamps. Intervals can be divided into two different categories, long and short intervals. Long intervals are measured in years and/or months. Short intervals are measured in days, hours, minutes and/or seconds. They are different in the sense that a short interval may not be cast to a long and vice versa.

The table below shows all interval data types. n denotes precision and f fractional digits. None of these are mandatory and the default for n is always 2 and for f 6.

Suppose you are doing research on the correlation between the age of people and their fitness. To accomplish this you have instructed various people to walk or run 5 kilometers, store their ages and the time it took for them to complete the course. For this purpose, the following table definition might be appropriate:

CREATE TABLE my_fitness_table (
             age INTERVAL YEAR(3),
             fitness INTERVAL MINUTE(3) TO SECOND(0))

The above table will contain a column for ages in years up to 999 (maybe one of your research objects is 100 years old) and another for the course time. The course time allows times up to 999 minutes 59 seconds to be stored. Since we aren’t interested in fractions of seconds, they are omitted.

Function

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 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 standard, the standards committee settled on the current Gregorian calendar from year 1 to 9999.

Benefits

Mimer SQL implements support for the standard SQL temporal data types.

Links

See the Reference Manual of the Mimer SQL Documentation set.