How does the date difference query work?

Subtracting two dates in PostgreSQL gives a whole number of days. Subtracting two timestamps gives an interval; EXTRACT(EPOCH FROM interval) converts it to seconds, which you can divide into hours. AGE returns a calendar-aware interval in years, months and days.

What commonly goes wrong with date difference in SQL?

Casting to date counts midnight boundaries, so 23:59 to 00:01 is one day; use hours if you need elapsed time.

Date Difference in SQL

A date difference measures the time between two dates or timestamps, in days, hours or whole months, such as time to ship or time to resolve.

Example query

This example assumes: orders(id, ordered_at timestamptz, shipped_at timestamptz, delivered_at timestamptz). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT id,
       shipped_at::date - ordered_at::date AS days_to_ship,
       EXTRACT(EPOCH FROM (shipped_at - ordered_at)) / 3600 AS hours_to_ship,
       AGE(delivered_at, ordered_at) AS elapsed_to_delivery
FROM orders
WHERE shipped_at IS NOT NULL
ORDER BY days_to_ship DESC;

How it works

Subtracting two dates in PostgreSQL gives a whole number of days.
Subtracting two timestamps gives an interval; EXTRACT(EPOCH FROM interval) converts it to seconds, which you can divide into hours.
AGE returns a calendar-aware interval in years, months and days.

MySQL, BigQuery, SQL Server and SQLite differences

MySQL: DATEDIFF(shipped_at, ordered_at) for days, TIMESTAMPDIFF(HOUR, ordered_at, shipped_at) for other units.
SQL Server: DATEDIFF(day, ordered_at, shipped_at). BigQuery: DATE_DIFF(a, b, DAY) or TIMESTAMP_DIFF(a, b, HOUR). SQLite: julianday(a) - julianday(b).

Common mistakes

Casting to date counts midnight boundaries, so 23:59 to 00:01 is one day; use hours if you need elapsed time.
Null end dates (not shipped yet) should be excluded or reported separately, not treated as zero.
In SQL Server, DATEDIFF also counts boundaries crossed, not elapsed whole units.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

How many days does an order take to ship on average?
Which orders took more than 5 days to be delivered?