How to Calculate Date Differences in PostgreSQL

The return types differ and that is the usual source of surprises. date minus date is an integer count of days. timestamp minus timestamp is an interval, which prints as something like 41 days 03:12:00 and does not compare cleanly against a number. age() converts that into calendar units, and extract(epoch ...) flattens an interval to seconds so you can divide it into whatever unit you need.

SELECT
  current_date - '2026-01-01'::date              AS days_elapsed,
  now() - created_at                              AS raw_interval,
  age(now(), created_at)                          AS calendar_diff,
  extract(epoch FROM now() - created_at) / 3600   AS hours_elapsed
FROM orders
LIMIT 5;

Filter by age

Keep the arithmetic on the constant side of the comparison. Written this way, Postgres can use an index on created_at. Moving the function onto the column instead, as in age(created_at) > interval '30 days', forces a sequential scan over the whole table.

SELECT id, created_at
FROM orders
WHERE created_at < now() - interval '30 days';