Documentation
/ SQL
/ Functions
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
.
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 |
'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
.
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 |
Last modified: 2024-07-22