Informix® provides two
data types to store dates and time information:
- DATE = for year, month and day storage.
- DATETIME = for year to fraction(1-5) storage.
PostgreSQL provides the following data type to store date and time
information:
- 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.
String representing date time information
Informix is able to convert quoted strings to DATE /
DATETIME data if the string contents matches environment parameters (i.e. DBDATE, GL_DATETIME). As
in Informix, PostgreSQL can convert quoted strings to
date time data according to the DateStyle session parameter. PostgreSQL always accepts ISO date time
strings.
Date arithmetic
- Informix supports date arithmetic on DATE and
DATETIME values. The result of an arithmetic expression involving dates/times is a number of days
when only DATEs are used, and an INTERVAL value if a DATETIME is used in the expression.
- In PostgreSQL, the result of an arithmetic expression involving DATE values is an INTEGER
representing a number of days.
- Informix automatically converts an integer to a date
when the integer is used to set a value of a date column. PostgreSQL does not support this automatic
conversion.
- Complex DATETIME expressions ( involving INTERVAL values for example) are Informix-specific and
have no equivalent in PostgreSQL.
Solution
The DATE type of PostgreSQL is equivalent to the DATE type in Informix ( stores year, month, day ). Use PostgreSQL
DATE data type for Informix DATE
columns.
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.
See also Date and time in SQL statements for good SQL programming
practices.