Dates & time

Extract parts, filter by date, and do date math.

Working with dates

Postgres has first-class date, timestamp, and interval types. Useful tools:

CURRENT_DATE, now() — today / this instant
EXTRACT(YEAR FROM d), EXTRACT(MONTH FROM d) — pull out a part
date_trunc('month', d) — round down to the start of a unit
age(d), d + INTERVAL '7 days' — date arithmetic
to_char(d, 'YYYY-MM') — format as text

SELECT id, ordered_at + INTERVAL '30 days' AS due FROM orders;

EXTRACT pulls a number out of a date, date_trunc rounds a date down to the start of a unit, and adding an interval gives another date. Only to_char returns text.