PostgreSQL DATE Data Type

PostgreSQL DATE stores a calendar date without a time of day. It occupies 4 bytes, has one-day precision, and supports dates from 4713 BC through 5874897 AD.

PostgreSQL accepts many date input formats. For unambiguous input in SQL statements and application code, prefer the ISO 8601 YYYY-MM-DD format; numeric forms such as 1/8/1999 can mean different dates depending on the DateStyle setting. See PostgreSQL’s date/time input documentation.

PostgreSQL DATE syntax

To create a column of DATE type, use the following syntax:

column_name DATE column_constraint

You can insert a date literal in ISO 8601 format into a DATE column:

You can use type conversion to convert a text-formatted date value to a date type, as follows:

'2022-08-30'::DATE

PostgreSQL DATE Examples

Create a new table named test_date:

CREATE TABLE test_date (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  date_value DATE NOT NULL DEFAULT CURRENT_DATE
);

The test_date table includes two columns:

1. The id column is the primary key column that identifies the order, and it is an identity column.
2. The date_value column is of DATE data type, it cannot be null, and has the current date as the default value.

Insert DATE values

The following INSERT statement uses unambiguous YYYY-MM-DD literals to insert rows into the test_date table.

INSERT INTO test_date (date_value)
VALUES
  ('2022-08-29'),
  ('2022-08-30'),
  ('2022-08-31')
RETURNING *;

 id | date_value
----+------------
  1 | 2022-08-29
  2 | 2022-08-30
  3 | 2022-08-31
(3 rows)

Update DATE values

To update DATE values, use this UPDATE statement and pass in a value in the yyyy-mm-dd format:

UPDATE test_date
SET date_value = '2022-09-01'
WHERE id = 3
RETURNING *;

 id | date_value
----+------------
  3 | 2022-09-01
(1 row)

Use DATE value in WHERE condition

You can use the DATE column in the WHERE clause to filter rows.

To find the rows with date values 2022-08-30, use the following statement:

SELECT *
FROM test_date
WHERE date_value = '2022-08-30';

 id | date_value
----+------------
  2 | 2022-08-30
(1 row)

You can also find all rows with date_value less than 2022-09-01, as follows:

SELECT *
FROM test_date
WHERE date_value < '2022-09-01';

 id | date_value
----+------------
  1 | 2022-08-29
  2 | 2022-08-30
(2 rows)

format date values

PostgreSQL provides TO_CHAR() functions to format date values ​​in a specified format. The TO_CHAR() function accepts two parameters. The first parameter is the date value to format and the second parameter is the format.

To display the date in the yyyy/mm/dd format, use the following statement:

SELECT
  id,
  to_char(date_value, 'yyyy/mm/dd')
FROM
  test_date;

 id |  to_char
----+------------
  1 | 2022/08/29
  2 | 2022/08/30
  3 | 2022/09/01
(3 rows)

PostgreSQL Date functions

PostgreSQL provides many date functions to handle date values.

To get the current date, use current_date:

SELECT current_date;

 current_date
--------------
 2022-08-31

To calculate age from a date, the is age() function:

SELECT age('2001-01-01');

           age
-------------------------
 21 years 7 mons 30 days

To convert a date string in a specified format to a date value, use the to_date() function :

SELECT to_date('2022-08-31', 'YYYY-MM-DD');

  to_date
------------
 2022-08-31

To extract the year, month, and day parts of a date value, use the date_part() function :

SELECT
  date_part('year', current_date) "year",
  date_part('month', current_date) "month",
  date_part('day', current_date) "day";

 year | month | day
------+-------+-----
 2022 |     8 |  31

Conclusion

This article discussed the PostgreSQL DATE data type, as well as some commonly used DATE operators and functions.