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.