Dates · Easy
How do you group by month and measure time between two dates in SQL?
Short answer
Truncate the date to a month and group on that: strftime('%Y-%m', d) in SQLite, DATE_TRUNC('month', d) in PostgreSQL. For durations, subtract dates as day numbers, for example julianday(end) − julianday(start) in SQLite or end − start in PostgreSQL. Always exclude rows where either date is NULL, or understand that the result will be NULL.
Detailed answer
Task: average and slowest delivery time by month for delivered orders.
SELECT strftime('%Y-%m', order_date) AS month,
COUNT(*) AS delivered_orders,
ROUND(AVG(julianday(delivered_date) - julianday(order_date)), 1) AS avg_days_to_deliver,
MAX(julianday(delivered_date) - julianday(order_date)) AS slowest_days
FROM orders
WHERE status = 'delivered'
AND delivered_date IS NOT NULL
GROUP BY month
ORDER BY month;
Dialect cheat-sheet (good to say out loud; syntax differs, concepts do not):
| Need | SQLite | PostgreSQL | MySQL |
|---|---|---|---|
| Month bucket | strftime('%Y-%m', d) |
DATE_TRUNC('month', d) |
DATE_FORMAT(d, '%Y-%m-01') |
| Add 30 days | date(d, '+30 days') |
d + INTERVAL '30 days' |
DATE_ADD(d, INTERVAL 30 DAY) |
| Day difference | julianday(a) - julianday(b) |
a - b |
DATEDIFF(a, b) |
Pitfall: grouping by strftime('%m', d) (month number only) merges January 2025 with January 2026. Always include the year unless you deliberately want seasonality.