DATE, TIME, and TIMESTAMP Data Types
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 theDATE
data type in previous versions. When you back up an older database and restore it in version 6 and later, all theDATE
columns and domains are automatically restored asTIMESTAMP
. 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
andTIME
. These data types function as their names suggest:DATE
holds only date information andTIME
holds only time. - In dialect 3,
DATE
andTIME
columns require only four bytes of storage, whileTIMESTAMP
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