Informix® INTERVAL data type stores a value that
represents a span of time. INTERVAL types are divided into two classes: year-month intervals and
day-time intervals.
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(N) 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...) qualifier1 TO qualifier2.
- 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.