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

ORACLE

ORACLE provides an INTERVAL data type similar to Informix, implementing two classes (YEAR TO MONTH and DAY TO SECOND).

However, Oracle's intervals cannot be defined with a time units different from the two interval classes. For example, you cannot define an INTERVAL HOUR TO MINUTE in Oracle.

Note: The ORACLE INTERVAL DAY TO SECOND(n) contains the fractional part of seconds and therefore is equivalent to the Informix INTERVAL DAY TO FRACTION(n) type.

Solution

Informix INTERVAL YEAR(n) TO MONTH data is stored in Oracle INTERVAL YEAR(n) TO MONTH columns. These data types are equivalent.

Informix INTERVAL DAY(n) TO FRACTION(p) data is stored in Oracle INTERVAL DAY(n) TO SECOND(p) columns. These data types are equivalent.

Other Informix INTERVAL types must be stored in CHAR() columns, because the high qualifier precision cannot be specified with Oracle INTERVALs. For example, Informix INTERVAL HOUR(5) TO MINUTE has no native equivalent in Oracle.

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.