Date and time functions

Truncating, extracting, adding and subtracting dates, timestamps and intervals.

Syntax

date_trunc('month', ts)                  -- start of the month (timestamp)
extract(year FROM ts)  /  date_part('dow', ts)
ts + interval '7 days'   date + 7   date - date (integer days)
age(ts2, ts1)   ts2 - ts1 (interval)
make_date(y, m, d)   to_char(ts, 'YYYY-MM')   to_date(text, 'DD/MM/YYYY')
current_date   now() / current_timestamp   localtimestamp

Types: date (day), timestamp (date + time, no zone), timestamptz (an instant, shown in the session time zone), interval (a duration like '3 days 2 hours').

date_trunc('month', ts) rounds down to the unit ('day', 'week' = Monday, 'month', 'quarter', 'year'). Returns a timestamp; add ::date for a date. The standard way to group by period.
extract(field FROM ts) returns a number: year, month, day, hour, dow (0 = Sunday), isodow (1 = Monday … 7 = Sunday), week (ISO), epoch (seconds).
date - date is an integer number of days. timestamp - timestamp is an interval; turn it into hours with extract(epoch FROM t2 - t1) / 3600.
date + integer adds days; ts + interval '1 month' adds calendar months (31 Jan + 1 month = 28/29 Feb).
age(a, b) gives a symbolic interval in years/months/days — good for ages.
to_char(ts, 'YYYY-MM') formats for display (the result is text — do not sort or compare on it unless the format sorts correctly).
now() is the start time of the current transaction; clock_timestamp() changes during a statement.
For ranges, use half-open intervals: ts >= '2025-03-01' AND ts < '2025-04-01'.

Examples

Monthly totals with date_trunc shop

SELECT date_trunc('month', ordered_at)::date AS month, count(*) AS orders
FROM orders
WHERE ordered_at >= '2025-01-01' AND ordered_at < '2026-01-01'
GROUP BY month
ORDER BY month;

Twelve rows, one per month of 2025.

Differences between dates and timestamps shop

SELECT shipment_id,
       promised_date,
       delivered_at::date - promised_date AS days_late,
       round(extract(epoch FROM delivered_at - shipped_at) / 3600, 1) AS hours_in_transit
FROM shipments
WHERE delivered_at::date > promised_date
ORDER BY days_late DESC, shipment_id
LIMIT 5;

date - date gives whole days; timestamp - timestamp gives an interval, converted to hours via epoch.

Extract parts and format people

SELECT employee_id, hire_date,
       extract(year FROM hire_date) AS hire_year,
       to_char(hire_date, 'Dy DD Mon YYYY') AS pretty,
       extract(year FROM age(DATE '2025-12-31', date_of_birth)) AS age_at_year_end
FROM employees
ORDER BY employee_id
LIMIT 5;

age() returns years/months/days; extract(year ...) keeps the completed years.

Pitfalls

BETWEEN with a date upper bound on a timestamp column (misses the last day after 00:00).
Grouping by to_char(ts, 'Mon YYYY') and then sorting it alphabetically (Apr before Jan).
Using extract(day FROM t2 - t1) as a duration: it returns only the days field of the interval, not the total.
Mixing timestamp and timestamptz and getting time-zone shifts.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: DATEADD(day, 7, d), DATEDIFF(day, a, b), DATEPART, DATETRUNC (2022+), GETDATE()/SYSDATETIME(), FORMAT for display.
MySQL: DATE_ADD(d, INTERVAL 7 DAY), DATEDIFF(a, b) (days), DATE_FORMAT(d, '%Y-%m'); no date_trunc.
BigQuery: DATE_TRUNC(d, MONTH) — part comes second and unquoted; DATE_DIFF(a, b, DAY); separate DATE/DATETIME/TIMESTAMP functions.
Snowflake: DATE_TRUNC('month', d) like PostgreSQL; DATEADD(day, 7, d); DATEDIFF(day, a, b).
Oracle: TRUNC(d, 'MM'), ADD_MONTHS, MONTHS_BETWEEN; DATE includes a time of day; SYSDATE.
SQLite: No date type; dates are text or numbers handled with date(), datetime(), julianday() and strftime().