Netezza date/time data type representations

The following table lists the IBM® Netezza® data types for date and time values that are addressed by the data type helper API, and the "maximum safe" range values for each data type. Like all data types, Netezza temporal types have a specific range of supported values. Not all of the range limits are strictly enforced, but violating the range usually causes subsequent errors that might not be thrown immediately.
Table 1. Netezza temporal data types
Type Internal representation Range
Date int32 which represents the number of days before (-) or after (+) 1/1/2000 Min: -730,119 (1/1/0001)

Max: 2,921,939 (12/31/9999)

Time int64 which represents the number of microseconds between midnight and one microsecond before midnight Min: 0 (00:00:00.000000)

Max: 86,399,999,999 (23:59:59.999999)

TimeTZ int64 which represents time (see the Time type)

int32 which represents the offset, in seconds, sign reversed

For example, the offset of "+ 1 hour" is stored as -3600 (-1*60*60).

Time Min: 0 (00:00:00.000000)

Time Max: 86,399,999,999 (23:59:59.999999)

Offset Min: -46800 (+ 13:00:00)

Offset Max: 46740 (-12:59:00)

Offset must be a whole number of minutes (offset%60 = 0). Note the sign reversal.

Timestamp int64 which represents the number of microseconds before (-) or after (+) 00:00:00.0, 1/1/2000 Min: -63,082,281,600,000,000 (00:00:00, 1/1/0001)

Max: 252,455,615,999,999,999 (23:59:59.999999, 12/31/9999)

Interval int32 which represents the number of months (+/-)

int64 which represents the number of microseconds (+/-)

A configuration of a negative (-) months value and a positive (+) microseconds value, and vice versa, is possible and supported by the Netezza system.

Months Min: - 3,000,000 (- 250000 years)

Months Max: 3,000,000 (250000 years)

Microseconds Min: NONE (max signed int64)

Microseconds Max: NONE (min signed int64)

The microsecond value can be as large as the maximum int64 data type value and will overflow into negative values without error.

A month is always considered to contain 30 days.

The months and microseconds values are stored separately and there is no information exchange between them.

Each conversion function provides an optional boolean error write-to argument for easy checking. The optional argument is set to true when the data is out of range and false when the data is in range (and there are no other errors). The conversion routines throw an error when any of the passed references or pointers are null, or when the optional error argument is not supplied and there is an error.