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 MONTHNetezza®
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 formINTERVAL(999-99...) start-qualifier TO end-qualifier. - Netezza normalizes all
INTERVALvalues 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 }