EXTRACT
Retrieves sub-fields such as year or hour from date/time values and returns values of type NUMERIC
. EXTRACT
is intended for computational processing, rather than for formatting date/time values for display.
Behavior Type
- Immutable if the
specified date is a
TIMESTAMP
,DATE
, orINTERVAL
- Stable if the specified date is a
TIMESTAMPTZ
Syntax
EXTRACT ( field FROM date )
Parameters
field |
A constant value that specifies the sub-field to extract from date (see Field Values below). |
date |
The date to process, an expression that evaluates to one of the following data types:
|
Field Values
CENTURY
|
The century number. The first century starts at 0001-01-01 00:00:00 AD. This definition applies to all Gregorian calendar countries. There is no century number 0, you go from –1 to 1. |
DAY
|
The day (of the month) field (1–31). |
DECADE
|
The year field divided by 10. |
DOQ
|
The day within the current quarter. DOQ recognizes leap year days. |
DOW
|
Zero-based day of the week, where Sunday=0.
|
DOY
|
The day of the year (1–365/366) |
EPOCH
|
Specifies to return one of the following:
|
HOUR
|
The hour field (0–23). |
ISODOW
|
The ISO day of the week, an integer between 1 and 7 where Monday is 1. |
ISOWEEK
|
The ISO week of the year, an integer between 1 and 53. |
ISOYEAR
|
The ISO year. |
MICROSECONDS
|
The seconds field, including fractional parts, multiplied by 1,000,000. This includes full seconds. |
MILLENNIUM
|
The millennium number, where the first millennium is 1 and each millenium starts on 01-01-y001 . For example, millennium 2 starts on 01-01-1001. |
MILLISECONDS
|
The seconds field, including fractional parts, multiplied by 1000. This includes full seconds. |
MINUTE
|
The minutes field (0 - 59). |
MONTH
|
For TIMESTAMP values, the number of the month within the year (1 - 12) ; for interval values the number of months, modulo 12 (0 - 11). |
QUARTER
|
The calendar quarter of the specified date as an integer, where the January-March quarter is 1, valid only for TIMESTAMP values. |
SECOND
|
The seconds field, including fractional parts, 0–59, or 0-60 if the operating system implements leap seconds. |
TIME ZONE
|
The time zone offset from UTC, in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC. |
TIMEZONE_HOUR
|
The hour component of the time zone offset. |
TIMEZONE_MINUTE
|
The minute component of the time zone offset. |
WEEK
|
The number of the week of the calendar year that the day is in. |
YEAR
|
The year field. There is no 0 AD , so subtract BC years from AD years accordingly. |
Examples
Extract the day of the week and day in quarter from the current TIMESTAMP:
=> SELECT CURRENT_TIMESTAMP AS NOW; NOW ------------------------------- 2016-05-03 11:36:08.829004-04 (1 row) => SELECT EXTRACT (DAY FROM CURRENT_TIMESTAMP); date_part ----------- 3 (1 row)
=> SELECT EXTRACT (DOQ FROM CURRENT_TIMESTAMP); date_part ----------- 33 (1 row)
Extract the timezone hour from the current time:
=> SELECT CURRENT_TIMESTAMP; ?column? ------------------------------- 2016-05-03 11:36:08.829004-04 (1 row) => SELECT EXTRACT(TIMEZONE_HOUR FROM CURRENT_TIMESTAMP); date_part ----------- -4 (1 row)
Extract the number of seconds since 01-01-1970 00:00:
=> SELECT EXTRACT(EPOCH FROM '2001-02-16 20:38:40-08'::TIMESTAMPTZ); date_part ------------------ 982384720.000000 (1 row)
Extract the number of seconds between 01-01-1970 00:00 and 5 days 3 hours before:
=> SELECT EXTRACT(EPOCH FROM -'5 days 3 hours'::INTERVAL); date_part ---------------- -442800.000000 (1 row)
Convert the results from the last example to a TIMESTAMP:
=> SELECT 'EPOCH'::TIMESTAMPTZ -442800 * '1 second'::INTERVAL; ?column? ------------------------ 1969-12-26 16:00:00-05 (1 row)