Lessons · SQL · date ranges

Arithmetic on dates

Dates stored as ISO text sort correctly, but subtracting them needs a number: julianday(date) gives days as a number, so the difference of two is a number of days.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Customer lifetime, days between orders, the length of an outage, age at signup: nearly every date question is a difference of two dates.

How to think about it

Convert with julianday (SQLite) or the database's date functions, subtract, and CAST to an integer when you want whole days. Group first when the two dates are the MIN and MAX of a group.

Worked example

SELECT customer_id, CAST(julianday(MAX(placed)) - julianday(MIN(placed)) AS INTEGER) AS span_days

Days from the first order to the last, per customer.

FROM orders GROUP BY customer_id HAVING COUNT(*) >= 2;

Only customers with two or more orders have a span.

Your turn

Days since each customer joined, as of 2026-01-01.

SELECT name, CAST(julianday('2026-01-01') - (joined) AS INTEGER) FROM customers;

The trap

Subtracting two date strings directly gives 0 or nonsense, because text minus text is not a date operation. Convert first.

Practise date ranges on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.