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 00:00:00
+	addition of a variable INTERVAL	SELECT DATE '1992-03-22' + INTERVAL (d.days) DAY FROM (VALUES (5), (11)) d(days)	1992-03-27 00:00:00 and 1992-04-02 00:00:00
-	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 00:00:00
-	subtraction of a variable INTERVAL	SELECT DATE '1992-03-27' - INTERVAL (d.days) DAY FROM (VALUES (5), (11)) d(days)	1992-03-22 00:00:00 and 1992-03-16 00:00:00

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
date_add(date, interval)	Add the interval to the date and return a DATETIME value.
date_diff(part, startdate, enddate)	The number of part boundaries between startdate and enddate, inclusive of the larger date and exclusive of the smaller date.
date_part(part, date)	Get subfield (equivalent to extract).
date_sub(part, startdate, enddate)	The signed length of the interval between startdate and enddate, truncated to whole multiples of part.
date_trunc(part, date)	Truncate to specified precision.
dayname(date)	The (English) name of the weekday.
days_in_month(date)	The number of days in the month of the given date.
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.
julian(date)	Extract the Julian Day number from a date.
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 to a grid of width bucket_width. The grid is anchored at 2000-01-01[ + offset] when bucket_width is a number of months or coarser units, else 2000-01-03[ + offset]. Note that 2000-01-03 is a Monday.
time_bucket(bucket_width, date[, origin])	Truncate timestamptz to a grid of width bucket_width. The grid is anchored at the origin timestamp, which defaults to 2000-01-01 when bucket_width is a number of months or coarser units, else 2000-01-03. Note that 2000-01-03 is a Monday.
today()	Current date (start of current transaction) in the local time zone.

date_add(date, interval)

Add the interval to the date and return a DATETIME value.

Query

SELECT date_add(DATE '1992-09-15', INTERVAL 2 MONTH) AS date_add;

Result

 date_add--------------------- 1992-11-15 00:00:00

date_diff(part, startdate, enddate)

The number of part boundaries between startdate and enddate, inclusive of the larger date and exclusive of the smaller date. Alias: datediff.

Query

SELECT date_diff('month', DATE '1992-09-15', DATE '1992-11-14') AS date_diff;

Result

 date_diff-----------         2

date_part(part, date)

Get the subfield (equivalent to extract). Alias: datepart.

Query

SELECT date_part('year', DATE '1992-09-20') AS date_part;

Result

 date_part-----------      1992

date_sub(part, startdate, enddate)

The signed length of the interval between startdate and enddate, truncated to whole multiples of part. Alias: datesub.

Query

SELECT date_sub('month', DATE '1992-09-15', DATE '1992-11-14') AS date_sub;

Result

 date_sub----------        1

date_trunc(part, date)

Truncate to specified precision. Always returns a TIMESTAMP, even when the input is a DATE. Alias: datetrunc.

Query

SELECT date_trunc('month', DATE '1992-03-07') AS date_trunc;

Result

 date_trunc--------------------- 1992-03-01 00:00:00

dayname(date)

The (English) name of the weekday.

Query

SELECT dayname(DATE '1992-09-20') AS dayname;

Result

 dayname--------- Sunday

days_in_month(date)

The number of days in the month of the given date.

Query

SELECT days_in_month(DATE '1992-02-15') AS days_in_month;

Result

 days_in_month---------------            29

extract(part from date)

Get subfield from a date.

Query

SELECT extract('year' FROM DATE '1992-09-20') AS extract;

Result

 extract---------    1992

greatest(date, date)

The later of two dates.

Query

SELECT greatest(DATE '1992-09-20', DATE '1992-03-07') AS greatest;

Result

 greatest------------ 1992-09-20

isfinite(date)

Returns true if the date is finite, false otherwise.

Query

SELECT isfinite(DATE '1992-03-07') AS isfinite;

Result

 isfinite---------- t

isinf(date)

Returns true if the date is infinite, false otherwise.

Query

SELECT isinf(DATE '-infinity') AS isinf;

Result

 isinf------- t

julian(date)

Extract the Julian Day number from a date.

Query

SELECT julian(DATE '1992-09-20') AS julian;

Result

 julian--------- 2448886

last_day(date)

The last day of the corresponding month in the date.

Query

SELECT last_day(DATE '1992-09-20') AS last_day;

Result

 last_day------------ 1992-09-30

least(date, date)

The earlier of two dates.

Query

SELECT least(DATE '1992-09-20', DATE '1992-03-07') AS least;

Result

 least------------ 1992-03-07

make_date(year, month, day)

The date for the given parts.

Query

SELECT make_date(1992, 9, 20) AS make_date;

Result

 make_date------------ 1992-09-20

monthname(date)

The (English) name of the month.

Query

SELECT monthname(DATE '1992-09-20') AS monthname;

Result

 monthname----------- September

strftime(date, format)

Converts a date to a string according to the format string.

Query

SELECT strftime(DATE '1992-01-01', '%a, %-d %B %Y') AS strftime;

Result

 strftime--------------------- Wed, 1 January 1992

time_bucket(bucket_width, date[, offset])

Truncate date to a grid of width bucket_width. The grid is anchored at 2000-01-01[ + offset] when bucket_width is a number of months or coarser units, else 2000-01-03[ + offset]. Note that 2000-01-03 is a Monday.

Query

SELECT time_bucket(INTERVAL '2 months', DATE '1992-04-20', INTERVAL '1 month') AS time_bucket;

Result

 time_bucket------------- 1992-04-01

time_bucket(bucket_width, date[, origin])

Truncate timestamptz to a grid of width bucket_width. The grid is anchored at the origin timestamp, which defaults to 2000-01-01 when bucket_width is a number of months or coarser units, else 2000-01-03. Note that 2000-01-03 is a Monday.

Query

SELECT time_bucket(INTERVAL '2 weeks', DATE '1992-04-20', DATE '1992-04-01') AS time_bucket;

Result

 time_bucket------------- 1992-04-15

today()

Description	Current date (start of current transaction) in the local time zone.
Example	today()
Result	2022-10-08
Alias	current_date (no parentheses necessary)

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.