⌘+k ctrl+k
1.2.0 (stable)
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 extension 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