Documentation
/ SQL
/ Functions
Date Functions
This section describes functions and operators for examining and manipulating date values.
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 DATEs |
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.
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) |
Alias of date_diff. The number of partition boundaries between the dates. |
datepart(part, date) |
Alias of date_part. Get the subfield (equivalent to extract). |
datesub(part, startdate, enddate) |
Alias of date_sub. The number of complete partitions between the dates. |
datetrunc(part, date) |
Alias of date_trunc. Truncate to specified precision. |
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(bigint, bigint, bigint) |
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). |
| Description |
Current date (at start of current transaction). |
| Example |
current_date |
| Result |
2022-10-08 |
| Description |
Add the interval to the date. |
| Example |
date_add(DATE '1992-09-15', INTERVAL 2 MONTH) |
| Result |
1992-11-15 |
| Description |
The number of partition boundaries between the dates. |
| Example |
date_diff('month', DATE '1992-09-15', DATE '1992-11-14') |
| Result |
2 |
| Description |
Get the subfield (equivalent to extract). |
| Example |
date_part('year', DATE '1992-09-20') |
| Result |
1992 |
| Description |
The number of complete partitions between the dates. |
| Example |
date_sub('month', DATE '1992-09-15', DATE '1992-11-14') |
| Result |
1 |
| Description |
Truncate to specified precision. |
| Example |
date_trunc('month', DATE '1992-03-07') |
| Result |
1992-03-01 |
| Description |
Alias of date_diff. The number of partition boundaries between the dates. |
| Example |
datediff('month', DATE '1992-09-15', DATE '1992-11-14') |
| Result |
2 |
| Description |
Alias of date_part. Get the subfield (equivalent to extract). |
| Example |
datepart('year', DATE '1992-09-20') |
| Result |
1992 |
| Description |
Alias of date_sub. The number of complete partitions between the dates. |
| Example |
datesub('month', DATE '1992-09-15', DATE '1992-11-14') |
| Result |
1 |
| Description |
Alias of date_trunc. Truncate to specified precision. |
| Example |
datetrunc('month', DATE '1992-03-07') |
| Result |
1992-03-01 |
| Description |
The (English) name of the weekday. |
| Example |
dayname(DATE '1992-09-20') |
| Result |
Sunday |
| Description |
Get subfield from a date. |
| Example |
extract('year' FROM DATE '1992-09-20') |
| Result |
1992 |
| Description |
The later of two dates. |
| Example |
greatest(DATE '1992-09-20', DATE '1992-03-07') |
| Result |
1992-09-20 |
| Description |
Returns true if the date is finite, false otherwise. |
| Example |
isfinite(DATE '1992-03-07') |
| Result |
true |
| Description |
Returns true if the date is infinite, false otherwise. |
| Example |
isinf(DATE '-infinity') |
| Result |
true |
| Description |
The last day of the corresponding month in the date. |
| Example |
last_day(DATE '1992-09-20') |
| Result |
1992-09-30 |
| Description |
The earlier of two dates. |
| Example |
least(DATE '1992-09-20', DATE '1992-03-07') |
| Result |
1992-03-07 |
| Description |
The date for the given parts. |
| Example |
make_date(1992, 9, 20) |
| Result |
1992-09-20 |
| Description |
The (English) name of the month. |
| Example |
monthname(DATE '1992-09-20') |
| Result |
September |
| 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 |
| 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 |
| 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 |
| Description |
Current date (start of current transaction). |
| Example |
today() |
| Result |
2022-10-08 |
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.
Last modified: 2024-04-25
