DATETIME data type

Informix^® has the DATETIME data type to store year to fraction information.

DATETIME= for year to fraction of a second (1-5) storage.

Genero db 3.61has the following equivalents for Informix DATETIME:

TIME= for hour, minute, and second storage.
TIMESTAMP= for year, month, day, hour, min, second, and fraction of second storage.
DATETIME= Synonym for TIMESTAMP.

Genero db 3.80 supports DATETIME and INTERVAL types likes Informix:

DATETIME start TO end
INTERVAL start[(prec)] TO end

String representing date time information: Informix is able to convert quoted strings to DATETIME data, if the string formatting matches the formatting set by environment parameters (GL_DATETIME). Genero db can also convert quoted strings to DATE / TIME / TIMESTAMP; by default, date/time formats follow the ISO standard (2005-01-30).

Date arithmetic:

Informix supports date arithmetic on DATETIME values. The result of an arithmetic expression involving DATETIME expressions is an INTERVAL expression.
In Genero db 3.80, DATETIME arithmetic and INTERVAL arithmetic are supported by the server.

Solution

With Genero db 3.80:

DATETIME and INTERVAL types can be used as with Informix, since these types are supported as native server types.

With Genero db 3.61:

Informix DATETIME data with a precision from HOUR TO SECOND is stored in a Genero db TIME column. DATETIME data with any other precision is stored in Genero db TIMESTAMP columns. The database interface makes the conversion automatically. Missing date or time parts default to 1900-01-01 00:00:00. For example, when using a DATETIME HOUR TO MINUTE with the value of "11:45", the Genero db DATETIME value will be "1900-01-01 11:45:00".

Note:

Literal DATETIME expressions (i.e. DATETIME 1999-10-12 YEAR TO DAY) are not converted.
SQL Statements using expressions with EXTEND must be reviewed and adapted to the native syntax.