Level 4 · How long until items come back?

Time between two events is a core analyst metric: days to ship, days to return, days from signup to first purchase, time to resolve a ticket. It is also one of the least portable bits of SQL.

Subtracting dates

In DuckDB and Postgres, date - date returns an integer number of days:

SELECT DATE '2025-03-20' - DATE '2025-03-01';   -- 19

returned_on is a DATE but order_date is a TIMESTAMP. Subtracting a timestamp gives an interval like 18 days 09:12:00, which you cannot average as a plain number as easily. Cast the timestamp to a date first, so both sides are calendar days:

r.returned_on - CAST(o.order_date AS DATE) AS days_to_return

Casting answers “how many calendar days later”, which is how return windows are written in a policy. If you needed hours (for example, time to first response on a support ticket), you would keep the timestamps and use an epoch difference instead.

The function forms

database	days between start and end
DuckDB	end - start, or DATE_DIFF('day', start, end)
Postgres	end - start (dates); EXTRACT(EPOCH FROM end - start) / 86400 for timestamps
MySQL	DATEDIFF(end, start)
BigQuery	DATE_DIFF(end, start, DAY)
Snowflake, SQL Server	DATEDIFF(day, start, end)

Watch the argument order: MySQL and BigQuery put the end first, DuckDB and Snowflake put the start first. Getting it backward flips the sign, which is easy to miss when you only look at an average. A quick MIN() that turns out negative is the giveaway.

Adding time

To go the other way, add an interval: order_date + INTERVAL 30 DAY works in DuckDB, MySQL and BigQuery (as DATE_ADD in BigQuery), and Postgres writes INTERVAL '30 days'. That is how you would flag returns that came in after a 30-day window:

WHERE r.returned_on > CAST(o.order_date AS DATE) + INTERVAL 30 DAY

Average and maximum tell different stories

The average tells Rachel what is typical. The maximum tells her about the tail: if late returns regularly reach 40 days, a 30-day window would turn some of today’s accepted returns into refused ones. On the job you would also look at a percentile, for example QUANTILE_CONT(days, 0.9) in DuckDB or PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY days) in Postgres, and report “90% of returns arrive within N days”. That sentence is far more useful for a policy decision than an average.

Common mistakes

• Mixing DATE and TIMESTAMP without casting, then averaging intervals.
• Swapping the argument order in DATEDIFF-style functions between dialects.
• Averaging over the wrong grain. Here one row is one return, which is what Rachel asked for.