Date Functions

This section describes functions and operators for examining and manipulating date values.

Date Operators

The table below shows the available mathematical operators for DATE types.

Operator Description Example Result
+ addition of days (integers) DATE '1992-03-22' + 5 1992-03-27
+ addition of an INTERVAL DATE '1992-03-22' + INTERVAL 5 DAY 1992-03-27
- subtraction of DATEs DATE '1992-03-27' - DATE '1992-03-22' 5
- subtraction of an INTERVAL DATE '1992-03-27' - INTERVAL 5 DAY' 1992-03-22

Date Functions

The table below shows the available functions for DATE types. Dates can also be manipulated with the timestamp functions through type promotion.

Function Description Example Result
current_date Current date (at start of current transaction)    
date_diff(part, startdate, , enddate) The number of partition boundaries between the dates date_diff('month', DATE '1992-09-15', DATE '1992-11-14') 2
date_part(part, date) Get the subfield (equivalent to extract) date_part('year', DATE '1992-09-20') 1992
date_sub(part, startdate, , enddate) The number of complete partitions between the dates date_sub('month', DATE '1992-09-15', DATE '1992-11-14') 1
date_trunc(part, date) Truncate to specified precision date_trunc('month', DATE '1992-03-07') 1992-03-01
dayname(date) The (English) name of the weekday monthname(DATE '1992-09-20') Sunday
extract(part from date) Get subfield from a date extract('year' FROM DATE '1992-09-20') 1992
greatest(date, date) The later of two dates greatest(DATE '1992-09-20', DATE '1992-03-07') 1992-09-20
last_day(date) The last day of the month last_day(DATE '1992-09-20') 1992-09-30
least(date, date) The earlier of two dates least(DATE '1992-09-20', DATE '1992-03-07') 1992-03-07
monthname(date) The (English) name of the month monthname(DATE '1992-09-20') September
weekofyear(date) The week of the year weekofyear(DATE '1992-09-20') 38
dayofmonth(date) Day of month dayofmonth(DATE '1992-09-20') 20
last_day(date) Last day of the corresponding month in the date last_day(DATE '1992-09-20') 1992-09-30
weekday(date) Day of the week (Sunday = 0, Saturday = 6) weekday(DATE '1992-09-20') 0
isodow(date) ISO day of the week (Monday = 1, Sunday = 7) isodow(DATE '1992-09-20') 7
yearweek(date) Year and week of year yearweek(DATE '1992-09-20') 199238
strftime(date, format) Converts a date to a string according to the format string strftime(date '1992-01-01', '%a, %-d %B %Y') Wed, 1 January 1992

There are also dedicated extraction functions to get the subfields.