DATE_PART

Extracts a sub-field such as year or hour from a date/time expression, equivalent to the the SQL-standard function EXTRACT.

Behavior Type

  • Immutable if the specified date is a TIMESTAMP, DATE, or INTERVAL
  • Stable if the specified date is a TIMESTAMPTZ

Syntax

DATE_PART ( 'field', 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.

Notes

According to the ISO-8601 standard, the week starts on Monday, and the first week of a year contains January 4. Thus, an early January date can sometimes be in the week 52 or 53 of the previous calendar year. For example: 

=> SELECT YEAR_ISO('01-01-2016'::DATE), WEEK_ISO('01-01-2016'), DAYOFWEEK_ISO('01-01-2016');
 YEAR_ISO | WEEK_ISO | DAYOFWEEK_ISO
----------+----------+---------------
     2015 |       53 |             5
(1 row)

Examples

Extract the day value:

SELECT DATE_PART('DAY', TIMESTAMP '2009-02-24 20:38:40') "Day";
  Day 
-----
  24
(1 row)

Extract the month value:

SELECT DATE_PART('MONTH', '2009-02-24 20:38:40'::TIMESTAMP) "Month";
  Month 
-------
     2
(1 row)

Extract the year value:

SELECT DATE_PART('YEAR', '2009-02-24 20:38:40'::TIMESTAMP) "Year";
  Year 
------
 2009
(1 row)

Extract the hours:

SELECT DATE_PART('HOUR', '2009-02-24 20:38:40'::TIMESTAMP) "Hour";
  Hour 
------
   20
(1 row)

Extract the minutes:

SELECT DATE_PART('MINUTES', '2009-02-24 20:38:40'::TIMESTAMP) "Minutes";
  Minutes 
---------
      38
(1 row)

Extract the day of quarter (DOQ):

SELECT DATE_PART('DOQ', '2009-02-24 20:38:40'::TIMESTAMP) "DOQ";
 DOQ
-----
  55
(1 row)

See Also

TO_CHAR