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

EXTRACT's day of week numbering differs from the function TO_CHAR .
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)