Date Parts

The date_part and date_diff and date_trunc functions can be used to manipulate the fields of temporal types. The fields are specified as strings that contain the part name of the field.

Part Specifiers

Below is a full list of all available date part specifiers. The examples are the corresponding parts of the timestamp 2021-08-03 11:59:44.123456.

Specifier Description Synonyms Example
'year' Gregorian year 'y', 'years' 2021
'month' Gregorian month 'mon', 'months', 'mons' 8
'day' Gregorian day 'days', 'd' 3
'decade' Gregorian decade 'decades' 202
'century' Gregorian century 'centuries' 21
'millennium' Gregorian millennium 'millenia' 3
'microseconds' Sub-minute microseconds 'microsecond' 44123456
'milliseconds' Sub-minute milliseconds 'millisecond', 'ms', 'msec', 'msecs' 44123
'second' Seconds 'seconds', 's' 44
'minute' Minutes 'minutes', 'm' 59
'hour' Hours 'hours', 'h' 11
'epoch' Seconds since 1970-01-01   1627991984
'dow' Day of the week (Sunday = 0, Saturday = 6)   2
'isodow' ISO day of the week (Monday = 1, Sunday = 7)   2
'week' Week number 'weeks', 'w' 31
'yearweek' Year and week number in YYYYWW format   202131
'dayofyear' Day of the year (1-365/366) 'doy' 215
'quarter' Quarter of the year (1-4) 'quarters' 3

The parts above 'epoch' are also used to specify INTERVAL types.

There are dedicated extraction functions to get the certain subfields:

Function Description Example Result
day(date) Day day(date '1992-02-15') 15
month(date) Month month(date '1992-02-15') 2
dayofyear(date) Day of year dayofyear(date '1992-02-15') 46
week(date) Week week(date '1992-02-15') 7
quarter(date) Quarter quarter(date '1992-02-15') 1
year(date) Year year(date '1992-02-15') 1992