helpinghand
search
needassistance
 
Features
Temporal Data Types and Mimer SQL
Category: 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 document describes the temporal data types that are defined in SQL-92 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 SQL-92 compatible date, time, timestamp and interval literals.

  • Fully SQL-92 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.

Later on in this document 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. SQL-92 defines dates to assume values denoting every day from year 1 to year 9999. The data type name is DATE, so if you want to create a table with a column containing dates you should enter:

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, if you want to create a table with a column containing times with milliseconds you should enter:

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, if you want to create a table with a column containing timestamps with microseconds you should enter:

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.
Long intervalsShort intervals
INTERVAL YEAR[(n)]INTERVAL DAY[(n)]
INTERVAL MONTH[(n)]INTERVAL HOUR[(n)]
INTERVAL YEAR[(n)] TO MONTHINTERVAL MINUTE[(n)]
 INTERVAL SECOND[(n[,f])]
 INTERVAL DAY[(n)] TO HOUR
 INTERVAL DAY[(n)] TO MINUTE
 INTERVAL DAY[(n)] TO SECOND[(f)]
 INTERVAL HOUR[(n)] TO MINUTE
 INTERVAL HOUR[(n)] TO SECOND[(f)]
 INTERVAL MINUTE[(n)] TO SECOND[(f)]


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 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 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.

Benefits

Mimer SQL implements support for the SQL-92 temporal data types.

Links

Mimer SQL Documentation Set, HTML navigation. (See the Reference Manual.)

Mimer SQL Documentation Set, PDF-format. (See the Reference Manual.)


Last updated: 2010-02-03

 

Powered by Mimer SQL

Powered by Mimer SQL