Documentation
/ SQL
/ Functions
Date Part Functions
The date_part, date_trunc, and date_diff functions can be used to extract or manipulate parts of temporal types such as TIMESTAMP, TIMESTAMPTZ, DATE and INTERVAL.
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, which returns a DOUBLE, 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 |
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 |
| 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 |
julian |
Julian Day number. |
|
2459430.4998162435 |
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.
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 |
© 2025 DuckDB Foundation, Amsterdam NL
