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';

The DATEDIFF equivalent

Postgres has no DATEDIFF function. Coming from SQL Server or MySQL, these are the equivalents per unit: whole days by subtracting dates, hours or minutes by flattening the interval with extract(epoch ...), and calendar months via age().

SELECT
  ('2026-08-20'::date - '2026-01-01'::date)                            AS diff_days,
  extract(epoch FROM ts2 - ts1) / 3600                                 AS diff_hours,
  extract(year FROM age(ts2, ts1)) * 12
    + extract(month FROM age(ts2, ts1))                                AS diff_months
FROM example;

Comparing dates safely

Comparisons work with plain operators, but two gotchas bite. First, BETWEEN with dates against a timestamp column stops at midnight, so BETWEEN '2026-08-01' AND '2026-08-31' silently drops everything on the 31st after 00:00. Use a half-open range instead. Second, keep the column bare in the comparison so an index on it stays usable.

-- half-open range: includes all of Aug 31
SELECT * FROM orders
WHERE created_at >= '2026-08-01'
  AND created_at < '2026-09-01';

Business-style differences

age() answers "how old is this in calendar terms" (2 months 3 days), which is what people usually want for tenure and billing. For "how many calendar days apart regardless of time", cast timestamps to date before subtracting; the raw timestamp difference of 23:59 hours is zero whole days but crosses a date boundary.

SELECT
  age(now(), signup_at)                    AS tenure,
  now()::date - signup_at::date            AS calendar_days_apart
FROM users;

Subtraction, age() and EXTRACT give different answers

Subtracting two timestamps gives an exact interval counted in days, hours and seconds. age() gives a calendar interval in years, months and days, so it accounts for how long the months actually were, and it is the one to use for someone's age or a billing period. Subtracting two dates gives a plain integer count of days, with no interval involved. Mixing them up is what makes two "correct" queries disagree by a day.

SELECT timestamptz '2026-03-01' - timestamptz '2026-01-31' AS subtraction, -- 29 days
       age(timestamptz '2026-03-01', timestamptz '2026-01-31') AS age,     -- 1 mon 1 day
       date '2026-03-01' - date '2026-01-31'                    AS days;    -- 29

Common errors

SELECT delivered_at - created_at                              AS interval,
       EXTRACT(epoch FROM delivered_at - created_at) / 3600  AS hours,
       delivered_at::date - created_at::date                 AS whole_days
FROM orders;

SELECT created_at::timestamptz - delivered_at::timestamptz FROM orders;

Do more with PostgreSQL

• Export Postgres to Google Sheets on a schedule
• Postgres SQL editor in the browser