DATE, TIME, and TIMESTAMP Data Types

From InterBase
Jump to: navigation, search

Go Up to Features and Dialects


InterBase 6 and later dialect 3 replace the old InterBase DATE data type, which contains both date and time information, with SQL-92 standard TIMESTAMP, DATE, and TIME data types. The primary migration problem exists in the source code of application programs that use the InterBase 5 DATE data type. In InterBase 6 and later, the DATE keyword represents a date-only data type, while a Version 5 DATE represents a date-and-time data type.

Columns and domains that are defined as DATE data type in InterBase 5 DATE appear as TIMESTAMP columns when the database is restored in InterBase 6. However, a TIMESTAMP data type has four decimal points of precision, while a Version 5 DATE data type has only two decimal points of precision.

If you migrate your database to dialect 3 and you require only date or only time information from a TIMESTAMP column, you can use ALTER COLUMN to change the data type to DATE or TIME. These columns each take only four bytes, whereas TIMESTAMP and the InterBase 5 DATE columns each take eight bytes. If your TIMESTAMP column holds both date and time information, you cannot change it to an InterBase 6 and later DATE or TIME column using ALTER COLUMN, because ALTER COLUMN does not permit data loss. Dialect use also enforces certain rules:

  • In dialect 1, only TIMESTAMP is available. TIMESTAMP is the equivalent of the DATE data type in previous versions. When you back up an older database and restore it in version 6 and later, all the DATE columns and domains are automatically restored as TIMESTAMP. DATE and TIMESTAMP data types are both available and both mean the same thing in dialect 1.
  • In dialect 3, TIMESTAMP functions as in dialect 1, but two additional data types are available: DATE and TIME. These data types function as their names suggest: DATE holds only date information and TIME holds only time.
  • In dialect 3, DATE and TIME columns require only four bytes of storage, while TIMESTAMP columns require eight bytes.

The following example shows the differences between dialect 1 and dialect 3 clients when date information is involved.

Example: CREATE TABLE table1 (fld1 DATE, fld2 TIME); 

INSERT INTO table1 VALUES (CURRENT_DATE, CURRENT_TIME);

Using dialect 1 clients : 

SELECT * FROM table1;
Statement failed, SQLCODE = -804
Dynamic SQL Error
-SQL error code = -804
-datatype unknown
-Client SQL dialect 1 does not support reference to TIME data type

SELECT fld1 FROM table1;
Statement failed, SQLCODE = -206
Dynamic SQL Error
-SQL error code = -206
-Column unknown
-FLD1
-Client SQL dialect 1 does not support reference to DATE data type

Using dialect 3 clients : 

SELECT * FROM table1;
FLD1 FLD2
=========== =============
1999-06-25 11:32:30.0000
SELECT fld1 FROM table1;
FLD1
===========
1999-06-25

Example: CREATE TABLE table1 (fld1 TIMESTAMP); 

INSERT INTO table1 (fld1) VALUES (CURRENT_TIMESTAMP);
SELECT * FROM table1;

In dialect 1 : 

FLD1
===========
25-JUN-1999

In dialect 3 : 

FLD1
=========================
1999-06-25 10:24:35.0000

Example: SELECT CAST (fld1 AS CHAR(5)) FROM table1;

In dialect 1 : 

======
25-JU

In dialect 3 : 

Statement failed, SQLCODE = -802
arithmetic exception, numeric overflow, or string truncation

Topics

Retrieved from "https://docwiki.embarcadero.com/InterBase/XE7/e/index.php?title=DATE,_TIME,_and_TIMESTAMP_Data_Types&oldid=23027"