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
, orINTERVAL
- 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:
|
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)