Date Part Functions
Copy Markdown
The date_part, date_trunc and date_diff functions can be used to extract or manipulate parts of temporal types such as TIMESTAMPTIMESTAMPTZDATEINTERVAL
The parts to be extracted or manipulated are specified by one of the strings in the tables below.
The example column provides the corresponding parts of the timestamp 2021-08-03 11:59:44.123456.
Only the entries of the first table can be extracted from INTERVALs or used to construct them.
Except for julian and epoch, which return DOUBLEs, all parts are extracted as integers. Since there are no infinite integer values in DuckDB, NULLs are returned for infinite timestamps.
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
1760465850.6698709
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
julianJulian Day number.
2459430.4998162435
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 DOUBLE Julian Day number.
Example julian(DATE '1992-09-20')
Result 2448886.0
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
