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.

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