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
julianandepoch, which returnDOUBLEs, all parts are extracted as integers. Since there are no infinite integer values in Goose,NULLs are returned for infinite timestamps.
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 | 1760465850.6698709 | |
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.
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). |
julian(date) | DOUBLE Julian Day number. |
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 |
julian(date)
| Description | DOUBLE Julian Day number. |
| Example | julian(DATE '1992-09-20') |
| Result | 2448886.0 |
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 |