Search Shortcut cmd + k | ctrl + k
Search cmd+k ctrl+k
1.0 (stable)
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`
`+` Addition of a variable `INTERVAL` `SELECT DATE '1992-03-22' + INTERVAL (d.days) DAY FROM (VALUES (5), (11)) AS d(days)` `1992-03-27` and `1992-04-02`
`-` Subtraction of `DATE`s `DATE '1992-03-27' - DATE '1992-03-22'` `5`
`-` Subtraction of an `INTERVAL` `DATE '1992-03-27' - INTERVAL 5 DAY` `1992-03-22`
`-` Subtraction of a variable `INTERVAL` `SELECT DATE '1992-03-27' - INTERVAL (d.days) DAY FROM (VALUES (5), (11)) AS d(days)` `1992-03-22` and `1992-03-16`

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.

Name Description
`current_date` Current date (at start of current transaction).
`date_add(date, interval)` Add the interval to the date.
`date_diff(part, startdate, enddate)` The number of partition boundaries between the dates.
`date_part(part, date)` Get the subfield (equivalent to `extract`).
`date_sub(part, startdate, enddate)` The number of complete partitions between the dates.
`date_trunc(part, date)` Truncate to specified precision.
`datediff(part, startdate, enddate)` The number of partition boundaries between the dates. Alias of `date_diff`.
`datepart(part, date)` Get the subfield (equivalent to `extract`). Alias of `date_part`.
`datesub(part, startdate, enddate)` The number of complete partitions between the dates. Alias of `date_sub`.
`datetrunc(part, date)` Truncate to specified precision. Alias of `date_trunc`.
`dayname(date)` The (English) name of the weekday.
`extract(part from date)` Get subfield from a date.
`greatest(date, date)` The later of two dates.
`isfinite(date)` Returns true if the date is finite, false otherwise.
`isinf(date)` Returns true if the date is infinite, false otherwise.
`last_day(date)` The last day of the corresponding month in the date.
`least(date, date)` The earlier of two dates.
`make_date(year, month, day)` The date for the given parts.
`monthname(date)` The (English) name of the month.
`strftime(date, format)` Converts a date to a string according to the format string.
`time_bucket(bucket_width, date[, offset])` Truncate `date` by the specified interval `bucket_width`. Buckets are offset by `offset` interval.
`time_bucket(bucket_width, date[, origin])` Truncate `date` by the specified interval `bucket_width`. Buckets are aligned relative to `origin` date. `origin` defaults to 2000-01-03 for buckets that don't include a month or year interval, and to 2000-01-01 for month and year buckets.
`today()` Current date (start of current transaction).

### `current_date`

 Description Current date (at start of current transaction). Example `current_date` Result `2022-10-08`

### `date_add(date, interval)`

 Description Add the interval to the date. Example `date_add(DATE '1992-09-15', INTERVAL 2 MONTH)` Result `1992-11-15`

### `date_diff(part, startdate, enddate)`

 Description The number of partition boundaries between the dates. Example `date_diff('month', DATE '1992-09-15', DATE '1992-11-14')` Result `2`

### `date_part(part, date)`

 Description Get the subfield (equivalent to `extract`). Example `date_part('year', DATE '1992-09-20')` Result `1992`

### `date_sub(part, startdate, enddate)`

 Description The number of complete partitions between the dates. Example `date_sub('month', DATE '1992-09-15', DATE '1992-11-14')` Result `1`

### `date_trunc(part, date)`

 Description Truncate to specified precision. Example `date_trunc('month', DATE '1992-03-07')` Result `1992-03-01`

### `datediff(part, startdate, enddate)`

 Description The number of partition boundaries between the dates. Example `datediff('month', DATE '1992-09-15', DATE '1992-11-14')` Result `2` Alias `date_diff`.

### `datepart(part, date)`

 Description Get the subfield (equivalent to `extract`). Example `datepart('year', DATE '1992-09-20')` Result `1992` Alias `date_part`.

### `datesub(part, startdate, enddate)`

 Description The number of complete partitions between the dates. Example `datesub('month', DATE '1992-09-15', DATE '1992-11-14')` Result `1` Alias `date_sub`.

### `datetrunc(part, date)`

 Description Truncate to specified precision. Example `datetrunc('month', DATE '1992-03-07')` Result `1992-03-01` Alias `date_trunc`.

### `dayname(date)`

 Description The (English) name of the weekday. Example `dayname(DATE '1992-09-20')` Result `Sunday`

### `extract(part from date)`

 Description Get subfield from a date. Example `extract('year' FROM DATE '1992-09-20')` Result `1992`

### `greatest(date, date)`

 Description The later of two dates. Example `greatest(DATE '1992-09-20', DATE '1992-03-07')` Result `1992-09-20`

### `isfinite(date)`

 Description Returns `true` if the date is finite, false otherwise. Example `isfinite(DATE '1992-03-07')` Result `true`

### `isinf(date)`

 Description Returns `true` if the date is infinite, false otherwise. Example `isinf(DATE '-infinity')` Result `true`

### `last_day(date)`

 Description The last day of the corresponding month in the date. Example `last_day(DATE '1992-09-20')` Result `1992-09-30`

### `least(date, date)`

 Description The earlier of two dates. Example `least(DATE '1992-09-20', DATE '1992-03-07')` Result `1992-03-07`

### `make_date(year, month, day)`

 Description The date for the given parts. Example `make_date(1992, 9, 20)` Result `1992-09-20`

### `monthname(date)`

 Description The (English) name of the month. Example `monthname(DATE '1992-09-20')` Result `September`

### `strftime(date, format)`

 Description Converts a date to a string according to the format string. Example `strftime(date '1992-01-01', '%a, %-d %B %Y')` Result `Wed, 1 January 1992`

### `time_bucket(bucket_width, date[, offset])`

 Description Truncate `date` by the specified interval `bucket_width`. Buckets are offset by `offset` interval. Example `time_bucket(INTERVAL '2 months', DATE '1992-04-20', INTERVAL '1 month')` Result `1992-04-01`

### `time_bucket(bucket_width, date[, origin])`

 Description Truncate `date` by the specified interval `bucket_width`. Buckets are aligned relative to `origin` date. `origin` defaults to `2000-01-03` for buckets that don't include a month or year interval, and to `2000-01-01` for month and year buckets. Example `time_bucket(INTERVAL '2 weeks', DATE '1992-04-20', DATE '1992-04-01')` Result `1992-04-15`

### `today()`

 Description Current date (start of current transaction). Example `today()` Result `2022-10-08`

## Date Part Extraction Functions

There are also dedicated extraction functions to get the subfields. A few examples include extracting the day from a date, or the day of the week from a date.

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