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
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.
Part by part
- date_trunc('month', created_at)
- Rounds a timestamp down to the first instant of its month. The result stays a timestamp, so it sorts and compares correctly rather than alphabetically.
- COUNT(*)
- Counts rows in each group, including those with NULL columns. COUNT of a specific column would skip rows where that column is NULL.
- GROUP BY month
- Grouping by the output alias. Postgres allows this; several other databases require repeating the whole expression instead.
Common mistakes
GROUP BY EXTRACT(MONTH FROM created_at)Extracting the month number discards the year, so January 2025 and January 2026 collapse into one group. The chart looks reasonable and merges every year of history.
GROUP BY date_trunc('month', created_at)GROUP BY TO_CHAR(created_at, 'Mon YYYY')Formatting to text before grouping means the result sorts alphabetically, so April precedes January. Truncate first, sort on the timestamp, and format only for display.
Assuming months with no rows will appearGROUP BY produces groups only for data that exists, so a month with no signups is simply absent rather than zero. A chart then joins the surrounding points and hides the gap entirely.
Questions
How do I include months with no data?
Generate the month series and LEFT JOIN the aggregate onto it — generate_series in Postgres, a calendar table elsewhere. Without that, absent months are invisible rather than zero.
What is the equivalent outside Postgres?
MySQL has DATE_FORMAT with a truncating pattern, SQL Server uses DATETRUNC or DATEFROMPARTS, and SQLite needs strftime. All achieve the same thing with different syntax and different sorting behaviour.
Which timezone does the truncation use?
The session's, for a timestamp with time zone. That means the same data can land in different months for different users, so pin the timezone explicitly in any report that must be reproducible.
Can I group by week instead?
Yes, date_trunc takes 'week' and starts on Monday in Postgres. Be careful comparing against systems that start the week on Sunday, since the two disagree by a day on every boundary.