# 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.