IBM PureData System for Analytics, Version 7.1

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).

IBM® Netezza® 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
Units	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		✓	✓	✓	✓			✓