Temporal data types

Use temporal data types to store date, time, and time-interval information. Although you can store this data in character strings, it is better to use temporal types for consistency and validation.

The following table describes the temporal data types.
Table 1. Temporal data types
Type Value Disk usage
date A month, day, and year. Values range from January 1, 0001, to December 31, 9999. 4 bytes
time An hour, minute, and second to six decimal places (microseconds). Values range from 00:00:00.000000 to 23:59:59.999999.

For more information, see Conversion functions.

8 bytes
time with time zone (alias timetz) An hour, minute, and second to six decimal places (microseconds), and the time zone offset from GMT. Values range from 00:00:00.000000+13:00 to 23:59:59.999999-12:59. 12 bytes
timestamp A month, day, year, hour, minute, and second to six decimal places (microseconds). Values range from January 1, 0001 00:00:00.000000 to December 31, 9999 23:59:59.999999. 8 bytes
interval (alias timespan) A time period that is specified using a single unit, such as 521 days or 521 seconds. This type is a nonstandard implementation. For more information, see Netezza SQL interval support 12 bytes
numeric(6,0) A time duration, that is, a numeric value that indicates a time period in hhmmss format. For example, the duration 010521 represents a time period of one hour, five minutes, and 21 seconds. 4 bytes
numeric(8,0) A date duration, that is, a numeric value that indicates a time period in YYYYMMDD format. For example, the duration 00010521 represents a time period of one year, five months, and 21 days. 4 bytes
numeric(14,0)
numeric(15,1)
numeric(16,2)
numeric(17,3)
numeric(18,4)
numeric(19,5)
numeric(20,6)
A timestamp duration, that is, a numeric value that contains 14-20 digits and indicates a time period:
  • The first 8 digits indicate the date portion of the duration in YYYYMMDD format.
  • The next 6 digits indicate the time portion of the duration in hhmmss format.
  • The next 0-6 digits indicate a fraction of a second.
For example, the duration 00017355018391999 in numeric(17,3) format represents a time period of one year, 73 months, 55 days, one hour, 83 minutes, and 91.999 seconds.
16 bytes