Date Part Functions
Copy Markdown
The date_part and date_diff and date_trunc functions can be used to manipulate the fields of temporal types such as DATETIMESTAMP
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
centuryGregorian century
cent, centuries, c21
dayGregorian day
days, d, dayofmonth3
decadeGregorian decade
dec, decades, decs202
hourHours
hr, hours, hrs, h11
microsecondsSub-minute microseconds
microsecond, us, usec, usecs, usecond, useconds44123456
millenniumGregorian millennium
mil, millenniums, millenia, mils, millenium3
millisecondsSub-minute milliseconds
millisecond, ms, msec, msecs, msecond, mseconds44123
minuteMinutes
min, minutes, mins, m59
monthGregorian month
mon, months, mons8
quarterQuarter of the year (1-4)
quarters3
secondSeconds
sec, seconds, secs, s44
yearGregorian year
yr, y, years, yrs2021
Specifier
Description
Synonyms
Example
dayofweekDay of the week (Sunday = 0, Saturday = 6)
weekday, dow2
dayofyearDay of the year (1-365/366)
doy215
epochSeconds since 1970-01-01
1627991984
eraGregorian era (CE/AD, BCE/BC)
1
isodowISO day of the week (Monday = 1, Sunday = 7)
2
isoyearISO Year number (Starts on Monday of week containing Jan 4th)
2021
timezone_hourTime zone offset hour portion
0
timezone_minuteTime zone offset minute portion
0
timezoneTime zone offset in seconds
0
weekWeek number
weeks, w31
yearweekISO 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.
There are dedicated extraction functions to get certain subfields:
Description Century.
Example century(DATE '1992-02-15')
Result 20
Description Day.
Example day(DATE '1992-02-15')
Result 15
Description Day (synonym).
Example dayofmonth(DATE '1992-02-15')
Result 15
Description Numeric weekday (Sunday = 0, Saturday = 6).
Example dayofweek(DATE '1992-02-15')
Result 6
Description Day of the year (starts from 1, i.e., January 1 = 1).
Example dayofyear(DATE '1992-02-15')
Result 46
Description Decade (year / 10).
Example decade(DATE '1992-02-15')
Result 199
Description Seconds since 1970-01-01.
Example epoch(DATE '1992-02-15')
Result 698112000
Description Calendar era.
Example era(DATE '0044-03-15 (BC)')
Result 0
Description Hours.
Example hour(timestamp '2021-08-03 11:59:44.123456')
Result 11
Description Numeric ISO weekday (Monday = 1, Sunday = 7).
Example isodow(DATE '1992-02-15')
Result 6
Description ISO Year number (Starts on Monday of week containing Jan 4th).
Example isoyear(DATE '2022-01-01')
Result 2021
Description Sub-minute microseconds.
Example microsecond(timestamp '2021-08-03 11:59:44.123456')
Result 44123456
Description Millennium.
Example millennium(DATE '1992-02-15')
Result 2
Description Sub-minute milliseconds.
Example millisecond(timestamp '2021-08-03 11:59:44.123456')
Result 44123
Description Minutes.
Example minute(timestamp '2021-08-03 11:59:44.123456')
Result 59
Description Month.
Example month(DATE '1992-02-15')
Result 2
Description Quarter.
Example quarter(DATE '1992-02-15')
Result 1
Description Seconds.
Example second(timestamp '2021-08-03 11:59:44.123456')
Result 44
Description Time zone offset hour portion.
Example timezone_hour(DATE '1992-02-15')
Result 0
Description Time zone offset minutes portion.
Example timezone_minute(DATE '1992-02-15')
Result 0
Description Time zone offset in minutes.
Example timezone(DATE '1992-02-15')
Result 0
Description ISO Week.
Example week(DATE '1992-02-15')
Result 7
Description Numeric weekday synonym (Sunday = 0, Saturday = 6).
Example weekday(DATE '1992-02-15')
Result 6
Description ISO Week (synonym).
Example weekofyear(DATE '1992-02-15')
Result 7
Description Year.
Example year(DATE '1992-02-15')
Result 1992
Description BIGINT of combined ISO Year number and 2-digit version of ISO Week number.
Example yearweek(DATE '1992-02-15')
Result 199207
© 2026 DuckDB Foundation, Amsterdam NL
