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 data types and PostgreSQL equivalents
Informix data type	PostgreSQL data type
`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 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.