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.

The values of Informix 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)
Informix is able to convert quoted strings to 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.
Note: Within Genero programs, the string representation for 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.

Informix provides the 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).
Note: The 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.

Netezza®

Netezza supports the following data types to store date/time values:

Table 1. Netezza date/time data types
Netezza data type Description
DATE for year, month, day storage
TIME for hour, minute, second, fraction with (6 decimal positions)
TIME WITH TIME ZONE / TIMETZ same as TIME, with time zone information
TIMESTAMP for year, month, day, hour, minute, second, fraction (with 6 decimal positions)

Like Informix, Netezza can convert quoted strings to date time data. Netezza accepts different date formats, including ISO date time strings, and you can specify the cast operator (::date, ::time, ::timestamp) after the string literal.

With Netezza, the result of an arithmetic expression involving DATE values is an INTEGER representing a number of days.

Complex DATETIME expressions (involving INTERVAL values for example) are Informix specific and have no equivalent in Netezza.

Solution

Use the following conversion rules to map Informix date/time types to Netezza date/time types:

Table 2. Informix data types and Netezza equivalents
Informix data type Netezza data type
DATE DATE
DATETIME HOUR TO MINUTE TIME
DATETIME HOUR TO SECOND TIME
DATETIME HOUR TO FRACTION(n) TIME
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
The 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.

Netezza and Informix DATE data type are equivalent and store year, month, day values.

Netezza TIME data type can be used to store Informix DATETIME HOUR TO FRACTION(n), DATETIME HOUR TO SECOND and DATETIME HOUR TO MINUTE values, and any other DATETIME type with qualifiers HOUR, MINUTE, SECOND, FRACTION(n). Missing time parts default to 00:00:00.0. For example, when using a DATETIME MINUTE TO SECOND with the value of "45:23", the Netezza TIME value will be "00:45:23.0".

Informix DATETIME values with any precision from YEAR to FRACTION(5) can be stored in Netezza TIMESTAMP columns. The database interface makes the conversion automatically. 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 Netezza TIMESTAMP value will be "1900-01-23 11:45:00.0".

Note: Informix supports implicit DATE/DATETIME conversions, for example you can use a DATE variable when the target column is a DATETIME. This is not possible with Netezza: The type of the SQL parameter must match the type of the column in the database table. Make sure that you are using the same type for the SQL parameter and the target column, DATE/DATETIME implicit conversion is not supported by Netezza.