DATE_PART

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

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