Date Functions
Version 0.4.0

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

Adding to or subtracting from infinite values produces the same infinite value.

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
datediff(part, startdate, enddate) Alias of date_diff. The number of partition boundaries between the dates datediff('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
datepart(part, date) Alias of date_part. Get the subfield (equivalent to extract) datepart('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
datesub(part, startdate, enddate) Alias of date_sub. The number of complete partitions between the dates datesub('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
datetrunc(part, date) Alias of date_trunc. Truncate to specified precision datetrunc('month', DATE '1992-03-07') 1992-03-01
dayname(date) The (English) name of the weekday dayname(DATE '1992-09-20') Sunday
isfinite(date) Returns true if the date is finite, false otherwise isfinite(DATE '1992-03-07') true
isinf(date) Returns true if the date is infinite, false otherwise isinf(DATE '-infinity') true
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 corresponding month in the date 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
make_date(bigint, bigint, bigint) The date for the given parts make_date(1992, 9, 20) 1992-09-20
monthname(date) The (English) name of the month monthname(DATE '1992-09-20') September
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.

Functions applied to infinite dates will either return the same infinite dates (e.g, greatest) or NULL (e.g., date_part) depending on what “makes sense”. In general, if the function needs to examine the parts of the infinite date, the result will be NULL.