PostgreSQL DATE Data Type

PostgreSQL supports DATE data types to store date values.

The PostgreSQL DATE type uses the yyyy-mm-dd format store date values, and each date value occupies 4 bytes.

PostgreSQL DATE syntax

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

column_name DATE column_constraint

You can insert text date values ​​in the yyyy-mm-dd format into date columns.

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

To insert values into a DATE column, you must ensure that the data is in the yyyy-mm-dd format . The following INSERT statement inserts several new 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 data_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.