EXTRACT

Retrieves sub-fields such as year or hour from date/time values and returns values of type NUMERIC.

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, or INTERVAL

  • Stable if the specified date is aTIMESTAMPTZ

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:

  • For DATE and TIMESTAMP values: the number of seconds before or since 1970-01-01 00:00:00-00 (if before, a negative number).

  • For INTERVAL values, the total number of seconds in the interval.

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)