Date Part Functions

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

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.

Part Specifiers Usable as Date Part Specifiers and in Intervals

Specifier	Description	Synonyms	Example
`'century'`	Gregorian century	`'cent'`, `'centuries'`, `'c'`	`21`
`'day'`	Gregorian day	`'days'`, `'d'`, `'dayofmonth'`	`3`
`'decade'`	Gregorian decade	`'dec'`, `'decades'`, `'decs'`	`202`
`'hour'`	Hours	`'hr'`, `'hours'`, `'hrs'`, `'h'`	`11`
`'microseconds'`	Sub-minute microseconds	`'microsecond'`, `'us'`, `'usec'`, `'usecs'`, `'usecond'`, `'useconds'`	`44123456`
`'millennium'`	Gregorian millennium	`'mil'`, `'millenniums'`, `'millenia'`, `'mils'`, `'millenium'`	`3`
`'milliseconds'`	Sub-minute milliseconds	`'millisecond'`, `'ms'`, `'msec'`, `'msecs'`, `'msecond'`, `'mseconds'`	`44123`
`'minute'`	Minutes	`'min'`, `'minutes'`, `'mins'`, `'m'`	`59`
`'month'`	Gregorian month	`'mon'`, `'months'`, `'mons'`	`8`
`'quarter'`	Quarter of the year (1-4)	`'quarters'`	`3`
`'second'`	Seconds	`'sec'`, `'seconds'`, `'secs'`, `'s'`	`44`
`'year'`	Gregorian year	`'yr'`, `'y'`, `'years'`, `'yrs'`	`2021`

Part Specifiers Only Usable as Date Part Specifiers

Specifier	Description	Synonyms	Example
`'dayofweek'`	Day of the week (Sunday = 0, Saturday = 6)	`'weekday'`, `'dow'`	`2`
`'dayofyear'`	Day of the year (1-365/366)	`'doy'`	`215`
`'epoch'`	Seconds since 1970-01-01		`1627991984`
`'era'`	Gregorian era (CE/AD, BCE/BC)		`1`
`'isodow'`	ISO day of the week (Monday = 1, Sunday = 7)		`2`
`'isoyear'`	ISO Year number (Starts on Monday of week containing Jan 4th)		`2021`
`'timezone_hour'`	Time zone offset hour portion		`0`
`'timezone_minute'`	Time zone offset minute portion		`0`
`'timezone'`	Time zone offset in seconds		`0`
`'week'`	Week number	`'weeks'`, `'w'`	`31`
`'yearweek'`	ISO year and week number in `YYYYWW` format		`202131`

Note that the time zone parts are all zero unless a time zone plugin such as ICU has been installed to support TIMESTAMP WITH TIME ZONE.

Part Functions

There are dedicated extraction functions to get certain subfields:

Name	Description
`century(date)`	Century.
`day(date)`	Day.
`dayofmonth(date)`	Day (synonym).
`dayofweek(date)`	Numeric weekday (Sunday = 0, Saturday = 6).
`dayofyear(date)`	Day of the year (starts from 1, i.e., January 1 = 1).
`decade(date)`	Decade (year / 10).
`epoch(date)`	Seconds since 1970-01-01.
`era(date)`	Calendar era.
`hour(date)`	Hours.
`isodow(date)`	Numeric ISO weekday (Monday = 1, Sunday = 7).
`isoyear(date)`	ISO Year number (Starts on Monday of week containing Jan 4th).
`microsecond(date)`	Sub-minute microseconds.
`millennium(date)`	Millennium.
`millisecond(date)`	Sub-minute milliseconds.
`minute(date)`	Minutes.
`month(date)`	Month.
`quarter(date)`	Quarter.
`second(date)`	Seconds.
`timezone_hour(date)`	Time zone offset hour portion.
`timezone_minute(date)`	Time zone offset minutes portion.
`timezone(date)`	Time Zone offset in minutes.
`week(date)`	ISO Week.
`weekday(date)`	Numeric weekday synonym (Sunday = 0, Saturday = 6).
`weekofyear(date)`	ISO Week (synonym).
`year(date)`	Year.
`yearweek(date)`	`BIGINT` of combined ISO Year number and 2-digit version of ISO Week number.

`century(date)`

Description	Century.
Example	`century(date '1992-02-15')`
Result	`20`

`day(date)`

Description	Day.
Example	`day(date '1992-02-15')`
Result	`15`

`dayofmonth(date)`

Description	Day (synonym).
Example	`dayofmonth(date '1992-02-15')`
Result	`15`

`dayofweek(date)`

Description	Numeric weekday (Sunday = 0, Saturday = 6).
Example	`dayofweek(date '1992-02-15')`
Result	`6`

`dayofyear(date)`

Description	Day of the year (starts from 1, i.e., January 1 = 1).
Example	`dayofyear(date '1992-02-15')`
Result	`46`

`decade(date)`

Description	Decade (year / 10).
Example	`decade(date '1992-02-15')`
Result	`199`

`epoch(date)`

Description	Seconds since 1970-01-01.
Example	`epoch(date '1992-02-15')`
Result	`698112000`

`era(date)`

Description	Calendar era.
Example	`era(date '0044-03-15 (BC)')`
Result	`0`

`hour(date)`

Description	Hours.
Example	`hour(timestamp '2021-08-03 11:59:44.123456')`
Result	`11`

`isodow(date)`

Description	Numeric ISO weekday (Monday = 1, Sunday = 7).
Example	`isodow(date '1992-02-15')`
Result	`6`

`isoyear(date)`

Description	ISO Year number (Starts on Monday of week containing Jan 4th).
Example	`isoyear(date '2022-01-01')`
Result	`2021`

`microsecond(date)`

Description	Sub-minute microseconds.
Example	`microsecond(timestamp '2021-08-03 11:59:44.123456')`
Result	`44123456`

`millennium(date)`

Description	Millennium.
Example	`millennium(date '1992-02-15')`
Result	`2`

`millisecond(date)`

Description	Sub-minute milliseconds.
Example	`millisecond(timestamp '2021-08-03 11:59:44.123456')`
Result	`44123`

`minute(date)`

Description	Minutes.
Example	`minute(timestamp '2021-08-03 11:59:44.123456')`
Result	`59`

`month(date)`

Description	Month.
Example	`month(date '1992-02-15')`
Result	`2`

`quarter(date)`

Description	Quarter.
Example	`quarter(date '1992-02-15')`
Result	`1`

`second(date)`

Description	Seconds.
Example	`second(timestamp '2021-08-03 11:59:44.123456')`
Result	`44`

`timezone_hour(date)`

Description	Time zone offset hour portion.
Example	`timezone_hour(date '1992-02-15')`
Result	`0`

`timezone_minute(date)`

Description	Time zone offset minutes portion.
Example	`timezone_minute(date '1992-02-15')`
Result	`0`

`timezone(date)`

Description	Time Zone offset in minutes.
Example	`timezone(date '1992-02-15')`
Result	`0`

`week(date)`

Description	ISO Week.
Example	`week(date '1992-02-15')`
Result	`7`

`weekday(date)`

Description	Numeric weekday synonym (Sunday = 0, Saturday = 6).
Example	`weekday(date '1992-02-15')`
Result	`6`

`weekofyear(date)`

Description	ISO Week (synonym).
Example	`weekofyear(date '1992-02-15')`
Result	`7`

`year(date)`

Description	Year.
Example	`year(date '1992-02-15')`
Result	`1992`

`yearweek(date)`

Description	`BIGINT` of combined ISO Year number and 2-digit version of ISO Week number.
Example	`yearweek(date '1992-02-15')`
Result	`199207`