DATE Data Type in PostgreSQL

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. Use DATE when the time of day is irrelevant — for ship-by dates, birth dates, event dates, and subscription renewal dates.

Syntax

column_name DATE

Common date input formats PostgreSQL accepts:

'2026-04-07'   -- ISO 8601 (recommended)
'April 7, 2026'
'04/07/2026'   -- ambiguous in some locales; avoid

Practical Example

Create an orders table with multiple date columns:

CREATE TABLE orders (
  id           SERIAL PRIMARY KEY,
  product_sku  VARCHAR(50)  NOT NULL,
  quantity     INTEGER      NOT NULL CHECK (quantity > 0),
  order_date   DATE         NOT NULL DEFAULT CURRENT_DATE,
  ship_by_date DATE         NOT NULL,
  delivered_on DATE
);

INSERT INTO orders (product_sku, quantity, ship_by_date)
VALUES
  ('SKU-001', 2, '2026-04-10'),
  ('SKU-002', 1, '2026-04-12'),
  ('SKU-003', 5, '2026-04-08');

SELECT * FROM orders;

 id | product_sku | quantity | order_date | ship_by_date | delivered_on
----+-------------+----------+------------+--------------+--------------
  1 | SKU-001     |        2 | 2026-04-07 | 2026-04-10   |
  2 | SKU-002     |        1 | 2026-04-07 | 2026-04-12   |
  3 | SKU-003     |        5 | 2026-04-07 | 2026-04-08   |

Date Functions

Get the current date:

SELECT CURRENT_DATE;        -- 2026-04-07
SELECT NOW()::date;         -- 2026-04-07

Format a date with TO_CHAR():

SELECT TO_CHAR(CURRENT_DATE, 'DD Mon YYYY');    -- 07 Apr 2026
SELECT TO_CHAR(CURRENT_DATE, 'Day, YYYY-MM-DD'); -- Tuesday , 2026-04-07

Find orders that ship within the next 3 days:

SELECT id, product_sku, ship_by_date
FROM orders
WHERE ship_by_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '3 days';

Calculate days until the ship-by date:

SELECT
  id,
  product_sku,
  ship_by_date,
  ship_by_date - CURRENT_DATE AS days_remaining
FROM orders
ORDER BY days_remaining;

AGE() and EXTRACT()

Calculate the age of an order:

SELECT
  id,
  order_date,
  AGE(ship_by_date, order_date) AS lead_time
FROM orders;

Extract individual date parts:

SELECT
  id,
  order_date,
  EXTRACT(YEAR  FROM order_date) AS year,
  EXTRACT(MONTH FROM order_date) AS month,
  EXTRACT(DAY   FROM order_date) AS day,
  EXTRACT(DOW   FROM order_date) AS day_of_week  -- 0=Sunday
FROM orders;

Testing with Vela

Date-range queries on large order tables are a common performance bottleneck. Before adding or modifying indexes on DATE columns in production, test the index change on a database branch populated with production data and use EXPLAIN ANALYZE to confirm the query plan switches from a sequential scan to an index scan.

Production Tips

• Always store dates using ISO 8601 format (yyyy-mm-dd) in SQL literals — locale-dependent formats like 01/02/2026 are ambiguous and error-prone.
• Use CURRENT_DATE as a default for event-date columns rather than NOW() to avoid accidentally capturing a time component.
• For date range queries, use >= and < comparisons rather than BETWEEN when the upper bound is exclusive — for example, WHERE order_date >= '2026-04-01' AND order_date < '2026-05-01'.
• Index DATE columns that are frequently used in WHERE clauses for range queries — B-tree indexes support <, >, BETWEEN, and = operators efficiently.
• Use AGE() for human-readable intervals and the subtraction operator (date1 - date2) when you only need the raw number of days.