Extract date and time values

The following table describes the date and time values:
Table 1. Date and time values
Value Description
epoch The number of seconds since 1970-01-01 00:00:00-00. The value can be positive or negative.
millennium/millenniums The millennium; for example, 2 for a date between 01 Jan 2000 and 31 Dec 2999.
century/centuries The number of full 100-year periods represented by the year; for example, 20 for a date between 01 Jan 2000 and 31 Dec 2099.
decade/decades The number of full 10-year periods represented by the year; for example, 201 for a date between 01 Jan 2010 and 31 Dec 2019.
year/years The year; for example, 2015.
quarter The quarter of the year (1 - 4) that the specified day is in.
month/months The number of the month within the year (1 - 12).
week The number of the week of the year (1 - 53) that the specified day is in. The value uses the ISO-8601 definition of a week, which begins on Monday; as a result, some years might have 53 weeks, and sometimes the first few days of January can be included as part of the 52nd or 53rd week of the previous year.
day/days The day of the month (1 - 31).
dow The day of the week, from 1 (Sunday) to 7 (Saturday).
doy The day of the year (1 - 366).
hour/hours The hour of the day (0 - 23).
minute/minutes The minute of the hour (0 - 59).
second/seconds The second of the minute, not including fractional parts (0 - 59).
millisecond/milliseconds The second of the minute, including fractional parts to one thousandth of a second, multiplied by 1000 (0 - 59999).
microsecond/microseconds The second of the minute, including fractional parts to one millionth of a second, multiplied by 1000000 (0 - 59999999).

Netezza Performance Server SQL does not support timezone* values.

For example:
SELECT EXTRACT(DAY FROM TIMESTAMP '2007-02-14 20:38:40.24');
Result: 14
SELECT EXTRACT(SECONDS FROM TIMESTAMP '2007-02-14 12:15:06.123456');
Result: 6
SELECT EXTRACT(MILLISECONDS FROM TIMESTAMP '2007-02-14 12:15:06.123456');
Result: 6123
SELECT EXTRACT(MICROSECONDS FROM TIMESTAMP '2007-02-14 12:15:06.123456');
Result: 6123456
SELECT DATE_PART('DAY', DATE '2007-02-18');
Result: 18
SELECT DATE_PART('YEAR', 20130710);
Result: 1970 (This is the year portion of the date that is 20130710 seconds after the epoch, 00:00:00 UTC on January 1, 1970.)
SELECT DATE_PART('HOUR', 075569::numeric(6,0));
Result: 7
SELECT DATE_PART('MONTH', 20150705::numeric(8,0));
Result: 7
Not all units can be used to extract values from a particular temporal data type. The following table indicates which units can be extracted from each temporal data type.
Table 2. Units that can be extracted from a temporal data type
Units Temporal Data Type
Date Time Timezone Timestamp Interval
Time
duration
Date
duration
Timestamp
duration
epoch ✓ ✓ ✓ ✓ ✓      
millennium/millenniums ✓     ✓ ✓   ✓ ✓
century/centuries ✓     ✓ ✓   ✓ ✓
decade/decades ✓     ✓ ✓   ✓ ✓
year/years ✓     ✓ ✓   ✓ ✓
quarter ✓     ✓ ✓   ✓ ✓
month/months ✓     ✓ ✓   ✓ ✓
week ✓     ✓ ✓      
day/days ✓     ✓ ✓   ✓ ✓
doy ✓     ✓ ✓      
dow ✓     ✓ ✓      
hour/hours   ✓ ✓ ✓ ✓ ✓   ✓
minute/minutes   ✓ ✓ ✓ ✓ ✓   ✓
second/seconds   ✓ ✓ ✓ ✓ ✓   ✓
millisecond/milliseconds   ✓ ✓ ✓ ✓     ✓
microsecond/microseconds   ✓ ✓ ✓ ✓     ✓