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.