Almost every business question has a time element: this month vs last month, orders in the last 30 days, days to deliver, customers who joined in a quarter. Date handling is where many SQL queries quietly go wrong.
Date function names differ between databases (PostgreSQL, MySQL, SQL Server, BigQuery, DuckDB). The ideas are the same; check your database's documentation for exact names. Examples here use PostgreSQL/DuckDB style.
Examples use a fictional gym chain, FitFort, with a memberships table: member_id, branch, joined_on, expires_on, last_visit_at.
Extract parts of a date
SELECT member_id,
EXTRACT(YEAR FROM joined_on) AS join_year,
EXTRACT(MONTH FROM joined_on) AS join_month,
EXTRACT(DOW FROM joined_on) AS weekday
FROM memberships;Useful for "which weekday do most people sign up?".
Truncate to a period
For monthly reports, truncate each date to the first day of its month:
SELECT DATE_TRUNC('month', joined_on) AS month, COUNT(*) AS new_members
FROM memberships
GROUP BY 1
ORDER BY 1;Grouping by the truncated date keeps the year attached. Grouping by EXTRACT(MONTH ...) alone would merge January of different years, a common mistake.
Add and subtract time
SELECT member_id, joined_on + INTERVAL '30 days' AS trial_ends
FROM memberships;Difference between dates
SELECT member_id, expires_on - joined_on AS membership_days
FROM memberships;(Other databases use functions such as DATEDIFF.)
Relative ranges: the last 30 days
SELECT COUNT(*) FROM memberships
WHERE last_visit_at >= CURRENT_DATE - INTERVAL '30 days';Filtering a month correctly
-- Safer than BETWEEN with timestamps
WHERE joined_on >= '2026-10-01' AND joined_on < '2026-11-01'With timestamp columns, BETWEEN '2026-10-01' AND '2026-10-31' misses everything after midnight on the 31st. A half-open range (>= start, < next start) avoids that.
Month-on-month change
Combine truncation with a window function:
WITH monthly AS (
SELECT DATE_TRUNC('month', joined_on) AS month, COUNT(*) AS joins
FROM memberships GROUP BY 1
)
SELECT month, joins,
joins - LAG(joins) OVER (ORDER BY month) AS change
FROM monthly;Text to date
Data from files often arrives as text. Convert it before doing date maths:
SELECT CAST('2026-10-15' AS DATE);
SELECT TO_DATE('15/10/2026', 'DD/MM/YYYY');Be careful with formats like 03/04/2026, which means different dates in different countries.
Gaps in time series
If a month has no rows, GROUP BY simply skips it, and charts silently join the neighbours. To show zeros, generate a list of months (or use a calendar table) and LEFT JOIN your counts onto it.
Practise
Our date functions set covers extracting, truncating and date arithmetic, and the LAG and running totals set builds the trend patterns on top.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: