DATE and DATETIME data types
Informix®
Informix provides two data types to store date and time information:
DATE
= for year, month and day storage.DATETIME
= for year to fraction (1-5) storage.
The DATE
type is stored as an INTEGER
with the number of days
since 1899/12/31.
The DATETIME
type can be defined with various time units, by specifying a start
and end qualifier. For example, you can define a datetime to store an hour-to-second time value with
DATETIME HOUR TO SECOND
.
DATETIME
can be represented with a character string literal, or as
DATETIME()
literals:'2017-12-24 15:45:12.345' -- a DATETIME YEAR TO FRACTION(3)
'15:45' -- a DATETIME HOUR TO MINUTE
DATETIME(2017-12-24 12:45) YEAR TO MINUTE
DATETIME(12:45:56.333) HOUR TO FRACTION(3)
DATE
/ DATETIME
data, if the string contains matching environment
parameters. The string to date conversion rules for DATE
is defined by the DBDATE
environment variable. The string to datetime format for DATETIME
is defined by the
GL_DATETIME environment variable.DATETIME
values is always ISO (YYYY-MM-DD hh:mm:ss.fffff
)
Informix supports date arithmetic on
DATE
and DATETIME
values. The result of an arithmetic expression
involving dates/times is an INTEGER
number of days when only DATE values are used,
and an INTERVAL
value if a DATETIME
is used in the expression.
Informix automatically converts an
INTEGER
to a DATE
when the integer is used to set a value of a
date column.
CURRENT [ q1 TO
q2 ]
operator, to get the system date/time on the server where the current database
is located. When no qualifiers are specified, CURRENT
returns a DATETIME
YEAR TO FRACTION(3)
. Informix also supports the SYSDATE
operator, which
returns the current system time as a DATETIME YEAR TO FRACTION(5)
.USEOSTIME
configuration parameter must be set to 1 in order to get the subsecond
precision in CURRENT
and SYSDATE
operators. See Informix
documentation for more details.IBM® DB2®
IBM DB2 provides the following data type to store date and time data:
IBM DB2 data type | Description |
---|---|
DATE |
for year, month, day storage |
TIME |
for hour, minutes, seconds storage |
TIMESTAMP |
for year, month, day, hour, minutes, seconds, fraction(12) storage |
Like Informix, IBM DB2 can convert quoted strings to dates, times or
timestamps. Only one format is possible: 'yyyy-mm-dd'
for dates,
'hh:mm:ss'
for times and 'yyyy-mm-dd hh:mm:ss:f'
for
timestamps.
In IBM DB2, the
result of a date/time arithmetic expression involving DATE
values is a number of
days with a decimal part representing the fraction of the day ( 0.5 = 12H00
,
2.00694444 = (2 + (10/1440)) = 2 days and 10 minutes
) ). The result of a date/time
arithmetic expression involving TIME
or TIMESTAMP
values is a
number of seconds with a decimal part representing the fraction of seconds.
Solution
Use the following conversion rules to map Informix date/time types to IBM DB2 date/time types:
Informix data type | IBM DB2 data type |
---|---|
DATE |
DATE |
DATETIME HOUR TO MINUTE |
TIME |
DATETIME HOUR TO SECOND |
TIME |
DATETIME HOUR TO FRACTION(n) |
TIMESTAMP (for fraction storage) |
DATETIME YEAR TO MONTH |
TIMESTAMP |
DATETIME YEAR TO DAY |
TIMESTAMP |
DATETIME YEAR TO HOUR |
TIMESTAMP |
DATETIME YEAR TO MINUTE |
TIMESTAMP |
DATETIME YEAR TO SECOND |
TIMESTAMP |
DATETIME YEAR TO FRACTION(n) |
TIMESTAMP |
DATE
and
DATETIME
types translation can be controlled with the following FGLPROFILE
entries:dbi.database.dsname.ifxemul.datatype.date = { true | false }
dbi.database.dsname.ifxemul.datatype.datetime = { true | false }
For more details see IBM Informix emulation parameters in FGLPROFILE.DB2 and Informix
DATE
data type are equivalent and store year, month, day values.
DB2 TIME
data type can be used to store
Informix DATETIME HOUR TO SECOND
and
DATETIME HOUR TO MINUTE
values, and any other DATETIME
type with
qualifiers HOUR
, MINUTE
, SECOND
, except
FRACTION(n)
, because the DB2 TIME
native type
does not store fraction of seconds. Missing time parts default to 00:00:00. For example, when using
a DATETIME MINUTE TO SECOND
with the value of "45:23", the DB2 TIME
value will be "00:45:23".
Informix DATETIME
values with any
qualifiers from YEAR
to FRACTION(5)
can be stored in DB2 TIMESTAMP
columns. Missing date or time parts
default to 1900-01-01 00:00:00.0. For example, when using a DATETIME DAY TO MINUTE
with the value of "23 11:45", the DB2
TIMESTAMP
value will be "1900-01-23 11:45:00.0".
- Using integers as a number of days in an expression with dates is not supported by IBM DB2. Check your code to
detect where you are using integers with
DATE
columns. - Literal
DATETIME
andINTERVAL
expressions (i.e.DATETIME (1999-10-12) YEAR TO DAY)
are not converted. - It is strongly recommended that you use BDL variables in dynamic SQL statements instead of
quoted strings representing
DATE
values. For example:LET stmt = "SELECT ... FROM customer WHERE creat_date >'", adate,"'"
is not portable, use a question mark place holder instead and OPEN the cursor USING adate:LET stmt = "SELECT ... FROM customer WHERE creat_date > ?"
- DATE arithmetic expressions using SQL parameters (USING variables) are not fully supported. The
next example generates an error at PREPARE
time:
SELECT ... WHERE datecol < ? +1
- SQL Statements using expressions with
TODAY
/CURRENT
/EXTEND
must be reviewed and adapted to the native syntax.
Date/time SQL functions
Informix | IBM DB2 |
---|---|
today |
current date |
current hour to second |
current time |
current year to fraction(5) |
current timestamp |