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.

PostgreSQL

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

Table 1. PostgreSQL date/time data types
Netezza® data type Description
DATE for year, month, day storage
TIME [(p)] [{with|without} time zone] for hour, minute, second and fraction of second storage
TIMESTAMP [(p)] [{with|without} time zone] for year, month, day, hour, minute, second and fraction of second storage
TIMESTAMP for year, month, day, hour, minute, second, fraction (with 6 decimal positions)

PostgreSQL can convert quoted strings to date time data depending on the DateStyle session parameter. PostgreSQL always accepts ISO date time strings.

With PostgreSQL, the date format can be defined with the SET DATESTYLE SQL command.

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

Solution

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

Table 2. Informix data types and PostgreSQL equivalents
Informix data type PostgreSQL data type
DATE DATE
DATETIME HOUR TO MINUTE TIME(0) WITHOUT TIME ZONE
DATETIME HOUR TO SECOND TIME(0) WITHOUT TIME ZONE
DATETIME HOUR TO FRACTION(p) TIME(p) WITHOUT TIME ZONE
DATETIME YEAR TO MINUTE TIMESTAMP(0) WITHOUT TIME ZONE
DATETIME YEAR TO SECOND TIMESTAMP(0) WITHOUT TIME ZONE
DATETIME YEAR TO FRACTION(p) TIMESTAMP(p) WITHOUT TIME ZONE
DATETIME q1 TO q2 (other than above) TIMESTAMP(p) WITHOUT TIME ZONE
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.

The PostgreSQL and Informix DATE types are equivalent and store year, month, day values.

Important: (FGL-4680): Starting with Genero 3.10.07, the PostgreSQL ODI driver can detect the current date format used in the SQL session, and you can safely use SET DATESTYLE if needed. Older versions of Genero PostgreSQL expect DATE values in ISO format. Do not change the DATE format with the SET DATESTYLE instruction with Genero versions before 3.10.07.

PostgreSQL TIME(N) WITHOUT TIME ZONE data type can be used to store DATETIME HOUR TO ??? values.

PostgreSQL TIMESTAMP(N) WITHOUT TIME ZONE data type can be used to store DATETIME YEAR TO ??? values.

The SQL Translator of the PostgreSQL driver makes the following conversions automatically for the DATETIME types:
  • DATETIME HOUR TO MINUTE is converted to PostgreSQL TIME(0) WITHOUT TIME ZONE (seconds set to 00).
  • DATETIME HOUR TO SECOND is converted to PostgreSQL TIME(0) WITHOUT TIME ZONE.
  • DATETIME HOUR TO FRACTION(n) is converted to PostgreSQL TIME(N) WITHOUT TIME ZONE.
  • DATETIME YEAR TO MINUTE is converted to PostgreSQL TIMSTAMP(0) WITHOUT TIME ZONE (seconds set to 00).
  • DATETIME YEAR TO SECOND is converted to PostgreSQL TIMESTAMP(0) WITHOUT TIME ZONE.
  • DATETIME YEAR TO FRACTION(n) is converted to PostgreSQL TIMESTAMP(N) WITHOUT TIME ZONE.

Other DATETIME types will be mapped to PostgreSQL TIMESTAMP(n) types. Missing date or time parts default to 1900-01-01 00:00:00.

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