Skip to content

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.