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

Netezza®

Netezza implements the INTERVAL data type in a different way than Informix does:

  • Netezza allows you to specify interval qualifiers (YEAR, MONTH, DAY, ...) but internally it always uses the same base type, storing values of any combination of units. Thus, there is no way to distinguish year-month intervals and day-time intervals with Netezza.
  • The precision of Netezza intervals includes fraction of seconds with up to 6 significant digits. However, it is not possible to specify the scale of a Netezza interval as with the Informix FRACTION(p) qualifier.
  • With Netezza, interval literals must be include the units, as "-923 days 11 hours 22 minutes", while Informix interval literals have the form INTERVAL(999-99...) start-qualifier TO end-qualifier.
  • Netezza normalizes all INTERVAL values to units of seconds, and considers a month to be thirty days for the purpose of interval comparisons. This approximation can lead to inaccuracies.

Solution

The Informix INTERVAL types of the day-time class can be mapped to the native Netezza INTERVAL type, for day to second time interval storage.

Since Netezza does not clearly distinguish year-month interval class, such types are converted to CHAR(50) by the Netezza driver.

Important: Netezza (V6 at the time of writing) has several bugs regarding the INTERVAL type; we do not recommend using this type until Netezza has fixed these problems.
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.