⌘+k ctrl+k
1.0
Search Shortcut cmd + k | ctrl + k
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
About this page