DATE

The PostgreSQL DATE type stores calendar dates without a time component. It uses 4 bytes and supports dates from 4713 BC to 5874897 AD, stored internally in the yyyy-mm-dd format.

Creating a table with DATE columns

CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  first_name  VARCHAR(255) NOT NULL,
  last_name   VARCHAR(255) NOT NULL,
  birth_date  DATE NOT NULL,
  hire_date   DATE NOT NULL
);

Use CURRENT_DATE as a default for automatic date stamping:

CREATE TABLE documents (
  document_id  SERIAL PRIMARY KEY,
  header_text  VARCHAR(255) NOT NULL,
  posting_date DATE NOT NULL DEFAULT CURRENT_DATE
);

INSERT INTO documents (header_text) VALUES ('Billing to customer XYZ') RETURNING *;

document_id |       header_text       | posting_date
-------------+-------------------------+--------------
          1 | Billing to customer XYZ | 2024-02-01

Working with date functions

Get the current date:

SELECT CURRENT_DATE;         -- 2024-02-01
SELECT NOW()::date;          -- 2024-02-01

Format a date with TO_CHAR():

SELECT TO_CHAR(CURRENT_DATE, 'dd/mm/yyyy');  -- 01/02/2024
SELECT TO_CHAR(CURRENT_DATE, 'Mon dd, yyyy'); -- Feb 01, 2024

Calculate the interval between two dates:

SELECT
  first_name,
  last_name,
  now() - hire_date AS tenure
FROM employees;

first_name | last_name |           tenure
------------+-----------+---------------------------
Shannon    | Freeman   | 6970 days 08:51:20.824847
Sheila     | Wells     | 7701 days 08:51:20.824847

Calculate age using AGE():

SELECT
  first_name,
  last_name,
  AGE(birth_date) AS age
FROM employees;

first_name | last_name |           age
------------+-----------+--------------------------
Shannon    | Freeman   | 44 years 1 mon
Sheila     | Wells     | 45 years 11 mons 24 days

Extract specific date parts with EXTRACT():

SELECT
  first_name,
  EXTRACT(YEAR  FROM birth_date) AS year,
  EXTRACT(MONTH FROM birth_date) AS month,
  EXTRACT(DAY   FROM birth_date) AS day
FROM employees;

first_name | year | month | day
------------+------+-------+-----
Shannon    | 1980 |     1 |   1
Sheila     | 1978 |     2 |   5

Production tips

• Use DATE for birth dates, hire dates, and any value where the time component is irrelevant — it is more storage-efficient and semantically clearer than TIMESTAMP.
• Always store dates in yyyy-mm-dd format in SQL literals — avoid locale-dependent formats like 01/02/2024 which can be ambiguous.
• Use CURRENT_DATE as a default for event-date columns rather than NOW() to avoid accidentally storing time components.
• For date range queries, use BETWEEN or >= / < comparisons; both are index-friendly on DATE columns.

Reference: PostgreSQL documentation — Date/Time Types.