INTERVAL data type

Informix®

Informix provides the INTERVAL data type to store a value that represents a span of time.

INTERVAL types are divided into two classes:
  • year-month intervals. For example: INTERVAL YEAR(5) TO MONTH
  • day-time intervals. For example: INTERVAL DAY(9) TO SECOND
INTERVAL columns can be defined with various time units, by specifying a start and end qualifier. For example, you can define an interval to store a number of hours and minutes with INTERVAL HOUR(n) TO MINUTE, where n defines the maximum number of digits for the hours unit.
The values of Informix INTERVAL can be represented with a character string literal, or as INTERVAL() literals:
'-9834 15:45:12.345'  -- an INTERVAL DAY(6) TO FRACTION(3)
'7623-11'   -- an INTERVAL YEAR(9) TO MONTH
INTERVAL(18734:45) HOUR(5) TO MINUTE
INTERVAL(-7634-11) YEAR(5) TO MONTH

PostgreSQL

PostgreSQL provides an INTERVAL data type which is equivalent to the Informix INTERVAL:

  • It is possible to specify the interval class / precision with YEAR, MONTH, DAY, HOUR, MINUTE and SECOND[(p)] fields.
  • Fractional part of seconds can be defined with up to 6 digits.
  • The interval value range is from -178000000 to +178000000 years.
  • Input and output format can be controlled with the SET interval style command.

Solution

Use the following conversion rules to map Informix numeric types to PostgreSQL numeric types:

Table 1. Informix numeric data types and PostgreSQL equivalents
Informix data type PostgreSQL equivalent
INTERVAL YEAR[(p)] TO MONTH INTERVAL YEAR TO MONTH
INTERVAL YEAR[(p)] TO YEAR INTERVAL YEAR
INTERVAL MONTH[(p)] TO MONTH INTERVAL MONTH
INTERVAL DAY[(p)] TO FRACTION(n) INTERVAL DAY TO SECOND(n)
INTERVAL DAY[(p)] TO SECOND INTERVAL DAY TO SECOND(0)
INTERVAL DAY[(p)] TO MINUTE INTERVAL DAY TO MINUTE
INTERVAL DAY[(p)] TO HOUR INTERVAL DAY TO HOUR
INTERVAL DAY[(p)] TO DAY INTERVAL DAY
INTERVAL HOUR[(p)] TO FRACTION(n) INTERVAL HOUR TO SECOND(n)
INTERVAL HOUR[(p)] TO SECOND INTERVAL HOUR TO SECOND(0)
INTERVAL HOUR[(p)] TO MINUTE INTERVAL HOUR TO MINUTE
INTERVAL HOUR[(p)] TO HOUR INTERVAL HOUR
INTERVAL MINUTE[(p)] TO FRACTION(n) INTERVAL MINUTE TO SECOND(n)
INTERVAL MINUTE[(p)] TO SECOND INTERVAL MINUTE TO SECOND(0)
INTERVAL MINUTE[(p)] TO MINUTE INTERVAL MINUTE
INTERVAL SECOND[(p)] TO FRACTION(n) INTERVAL SECOND(n)
INTERVAL SECOND[(p)] TO SECOND INTERVAL SECOND(0)
INTERVAL FRACTION[(p)] TO FRACTION(n) INTERVAL SECOND(n)

The PostgreSQL database interface converts the Informix-style INTERVAL type to the native PostgreSQL INTERVAL type.

Important: The PostgreSQL database driver forces the interval style session parameter to 'iso_8601', this is required to insert and fetch interval database with the libpq CAPI functions. You must not change this setting during program execution.

While PostgreSQL intervals support up to 9 digits for the higher unit like Informix, year values range from -178000000 to +178000000 only. This limitation exists in PostgreSQL 8.4 and maybe solved in future versions.

The INTERVAL types translation can be controlled with the following FGLPROFILE entry:
dbi.database.dsname.ifxemul.datatype.interval = { true | false }
For more details see IBM Informix emulation parameters in FGLPROFILE.