PatternSQL
Group by month
Roll timestamped rows up into monthly totals with date_trunc('month', created_at), the portable way to bucket a date before grouping.
Last updated
Pattern
SELECT
date_trunc('month', created_at) AS month,
COUNT(*) AS signups
FROM users
GROUP BY month
ORDER BY month;Why it’s written this way
date_trunc('month', created_at) rounds every timestamp down to midnight on the first of its month, so two rows created on different days of March end up with the identical value. GROUP BY month then collapses every row sharing that value into one — the mechanism that turns a table of individual signups into a monthly count.
Grouping on the truncated value, kept as a real timestamp rather than a formatted string, means the result still sorts correctly by ORDER BY month and can be reformatted for display afterward without losing information.
Edge cases to know
- →The function name and behavior aren't standard across engines: DuckDB, Postgres and Snowflake use date_trunc('month', col); MySQL has no direct equivalent and typically needs DATE_FORMAT(col, '%Y-%m-01'); SQLite uses strftime('%Y-%m', col), which returns text rather than a date.
- →Grouping by the alias month (rather than repeating date_trunc(...) in GROUP BY) works in DuckDB and Postgres but not every engine — some require the full expression written out again in the GROUP BY clause.
- →A month with zero rows produces no output row at all, not a row with a zero count — left-join against a generated calendar of months if the result needs to show explicit gaps.