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.

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		`1627991984.123456`
`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`

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 seconds.
`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)`

Century.

SQL

SELECT century(DATE '1992-02-15') AS century;

 century
---------
      20

`day(date)`

Day.

SQL

SELECT day(DATE '1992-02-15') AS day;

 day
-----
  15

`dayofmonth(date)`

Day (synonym).

SQL

SELECT dayofmonth(DATE '1992-02-15') AS dayofmonth;

 dayofmonth
------------
         15

`dayofweek(date)`

Numeric weekday (Sunday = 0, Saturday = 6).

SQL

SELECT dayofweek(DATE '1992-02-15') AS dayofweek;

 dayofweek
-----------
         6

`dayofyear(date)`

Day of the year (starts from 1, i.e., January 1 = 1).

SQL

SELECT dayofyear(DATE '1992-02-15') AS dayofyear;

 dayofyear
-----------
        46

`decade(date)`

Decade (year / 10).

SQL

SELECT decade(DATE '1992-02-15') AS decade;

 decade
--------
    199

`epoch(date)`

Seconds since 1970-01-01.

SQL

SELECT epoch(DATE '1992-02-15') AS epoch;

 epoch
-----------
 698112000

`era(date)`

Calendar era.

SQL

SELECT era(DATE '0044-03-15 (BC)') AS era;

 era
-----
   0

`hour(date)`

Hours.

SQL

SELECT hour(timestamp '2021-08-03 11:59:44.123456') AS hour;

 hour
------
   11

`isodow(date)`

Numeric ISO weekday (Monday = 1, Sunday = 7).

SQL

SELECT isodow(DATE '1992-02-15') AS isodow;

 isodow
--------
      6

`isoyear(date)`

ISO Year number (Starts on Monday of week containing Jan 4th).

SQL

SELECT isoyear(DATE '2022-01-01') AS isoyear;

 isoyear
---------
    2021

`julian(date)`

DOUBLE Julian Day number.

SQL

SELECT julian(DATE '1992-09-20') AS julian;

 julian
---------
 2448886

`microsecond(date)`

Sub-minute microseconds.

SQL

SELECT microsecond(timestamp '2021-08-03 11:59:44.123456') AS microsecond;

 microsecond
-------------
    44123456

`millennium(date)`

Millennium.

SQL

SELECT millennium(DATE '1992-02-15') AS millennium;

 millennium
------------
          2

`millisecond(date)`

Sub-minute milliseconds.

SQL

SELECT millisecond(timestamp '2021-08-03 11:59:44.123456') AS millisecond;

 millisecond
-------------
       44123

`minute(date)`

Minutes.

SQL

SELECT minute(timestamp '2021-08-03 11:59:44.123456') AS minute;

 minute
--------
     59

`month(date)`

Month.

SQL

SELECT month(DATE '1992-02-15') AS month;

 month
-------
     2

`quarter(date)`

Quarter.

SQL

SELECT quarter(DATE '1992-02-15') AS quarter;

 quarter
---------
       1

`second(date)`

Seconds.

SQL

SELECT second(timestamp '2021-08-03 11:59:44.123456') AS second;

 second
--------
     44

`timezone_hour(date)`

Time zone offset hour portion.

SQL

SELECT timezone_hour(TIMESTAMP '2021-08-03 11:59:44.123456') AS timezone_hour;

 timezone_hour
---------------
             0

`timezone_minute(date)`

Time zone offset minutes portion.

SQL

SELECT timezone_minute(TIMESTAMP '2021-08-03 11:59:44.123456') AS timezone_minute;

 timezone_minute
-----------------
               0

`timezone(date)`

Time zone offset in seconds.

SQL

SELECT timezone(TIMESTAMP '2021-08-03 11:59:44.123456') AS timezone;

 timezone
----------
        0

`week(date)`

ISO Week.

SQL

SELECT week(DATE '1992-02-15') AS week;

 week
------
    7

`weekday(date)`

Numeric weekday synonym (Sunday = 0, Saturday = 6).

SQL

SELECT weekday(DATE '1992-02-15') AS weekday;

 weekday
---------
       6

`weekofyear(date)`

ISO Week (synonym).

SQL

SELECT weekofyear(DATE '1992-02-15') AS weekofyear;

 weekofyear
------------
          7

`year(date)`

Year.

SQL

SELECT year(DATE '1992-02-15') AS year;

 year
------
 1992

`yearweek(date)`

BIGINT of combined ISO Year number and 2-digit version of ISO Week number.

SQL

SELECT yearweek(DATE '1992-02-15') AS yearweek;

 yearweek
----------
   199207

Part Specifiers Usable as Date Part Specifiers and in Intervals​

Part Specifiers Only Usable as Date Part Specifiers​

Part Functions​

century(date)​

day(date)​

dayofmonth(date)​

dayofweek(date)​

dayofyear(date)​

decade(date)​

epoch(date)​

era(date)​

hour(date)​

isodow(date)​

isoyear(date)​

julian(date)​

microsecond(date)​

millennium(date)​

millisecond(date)​

minute(date)​

month(date)​

quarter(date)​

second(date)​

timezone_hour(date)​

timezone_minute(date)​

timezone(date)​

week(date)​

weekday(date)​

weekofyear(date)​

year(date)​

yearweek(date)​

Part Specifiers Usable as Date Part Specifiers and in Intervals

Part Specifiers Only Usable as Date Part Specifiers

Part Functions

`century(date)`

`day(date)`

`dayofmonth(date)`

`dayofweek(date)`

`dayofyear(date)`

`decade(date)`

`epoch(date)`

`era(date)`

`hour(date)`

`isodow(date)`

`isoyear(date)`

`julian(date)`

`microsecond(date)`

`millennium(date)`

`millisecond(date)`

`minute(date)`

`month(date)`

`quarter(date)`

`second(date)`

`timezone_hour(date)`

`timezone_minute(date)`

`timezone(date)`

`week(date)`

`weekday(date)`

`weekofyear(date)`

`year(date)`

`yearweek(date)`