Grouping by Date Periods in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Date Truncation and Extraction, GROUP BY
Builds toward Running Totals and Cumulative Metrics
How do you group by date periods in SQL?
Grouping by date period means truncating timestamps to a calendar unit — month, week, day — and using the truncated value as the GROUP BY key. The result is one row per period with aggregated measures across all rows that fall within it.
Raw timestamps are almost never the right grouping key. An orders table with a ordered_at timestamp down to the millisecond has a unique timestamp for nearly every row. Grouping by the raw value produces one group per row — not aggregation at all. The useful question is "what happened in March?" not "what happened at 14:32:07.443 on March 3rd?"
How do you group by month in a SQL query?
date_trunc() reduces a timestamp to the start of the specified period. All timestamps in the same period truncate to the same value and group correctly.
SELECT
date_trunc('month', ordered_at)::date AS month,
COUNT(*) AS order_count,
SUM(total_amount) AS total_revenue
FROM orders
GROUP BY date_trunc('month', ordered_at)
ORDER BY monthSELECT
date_trunc('month', ordered_at)::date AS month,
COUNT(*) AS order_count,
SUM(total_amount) AS revenue
FROM orders
GROUP BY date_trunc('month', ordered_at)
ORDER BY monthThis produces one row per month with a count and revenue total. Every order in March 2024 truncates to 2024-03-01 00:00:00 and ends up in the same group.
What can you put in a GROUP BY clause?
The one thing that trips people up
PostgreSQL lets you group by the full expression, by the column's position, or by the output alias. All three of these do the same thing:
GROUP BY date_trunc('month', ordered_at)GROUP BY 1GROUP BY monthThe trap is what happens when an alias collides with a real column. An input column name wins over an output alias, so SELECT country AS city ... GROUP BY city groups by the table's own city column rather than by the alias you just defined, and the query fails because country is then neither grouped nor aggregated. Give an alias a name that no column in the query already carries and the ambiguity never arises.
WHERE and HAVING are the clauses that genuinely reject an alias, which is probably where the idea comes from. Both are resolved before the SELECT list, so at that point the name does not exist yet.
The output of date_trunc() is a timestamp, even when the input is a date. For display or downstream joins that expect a date type, cast after truncating:
date_trunc('month', ordered_at)::date AS monthWhich day does date_trunc treat as the start of a week?
Week truncation starts on Monday
date_trunc('week', ...) follows ISO week convention: weeks begin on Monday. If your reporting context expects Sunday-starting weeks, the truncation will group differently than expected. For most analytical work, aligning to ISO weeks is the practical choice. If you need Sunday weeks, that requires a workaround with date arithmetic.
Grouping by date period is the foundation for almost all time-series work. Period aggregates feed into running totals, period-over-period comparisons, and trend analysis — all of which need clean one-row-per-period output to start from.
Which precision values does date_trunc accept?
Available precision values
date_trunc() supports: 'microseconds', 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century'. You'll use 'day', 'week', 'month', 'quarter', and 'year' most often. 'quarter' truncates to the first day of the quarter (January 1, April 1, July 1, October 1), which is useful for quarterly reporting without manual CASE WHEN logic.
Multiple grouping levels in one query
You can compute period aggregates at different levels in the same query using CTEs. Compute daily totals in one CTE, then aggregate those to monthly in the next. This is cleaner than re-aggregating the raw table at two levels in the same GROUP BY, and it gives each layer a clear, readable name.
Practice Grouping by Date Periods in SQL
Scenario: Brightlane's finance team needs a monthly revenue summary to track order activity over time.
Task: Write a query to return each calendar month, the number of orders placed in that month, and the total orders revenue for that month.
Assumptions:
- The
orderstable holds one row per placed order, with the placement timestamp stored inordered_atand the order amount stored intotal_amount. - A calendar month is identified by its first day and covers every order placed within that month.
Output:
- One row per calendar month present in the data.
- Columns in this order:
month(the first day of the calendar month),order_count,revenue.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 Grouping by Date Periods practice problems
Write a query to return each calendar month, the number of orders placed in that month, and the total orders revenue for that month.
Write a query to return each calendar day and the total number of events recorded on that day.
Write a query to return each calendar week and the number of orders placed in that week.
Write a query to return each calendar quarter, the number of orders placed in that quarter, the total orders revenue, and the average value per order.
Write a query to return each calendar month and the number of purchase events recorded in that month.
Write a query to return the year, the month number from 1 through 12, and the total number of events for each year-month combination.
Write a query to return each calendar year, the number of orders placed in that year, the total orders revenue, and the average value per order.
Write a query to return each month-of-year and the total number of events recorded in that month-of-year across every year in the data.
Write a query to return each calendar day in January 2024 on which at least one order was placed, the number of orders placed on that day, and the total orders revenue.
Write a query to return the year, the quarter number from 1 through 4, the number of orders placed in that year-quarter, and the total orders revenue for that year-quarter.
Start learning to practice all 10 Grouping by Date Periods problems, with instant grading and mastery tracking.
Deeper guides on Grouping by Date Periods
- labelling months with to_char without breaking the sort
to_char() patterns with real output, the MM-for-minutes bug, and month labels that sort April first.
Common questions about Grouping by Date Periods
Should I group by DATE_TRUNC or by EXTRACT(MONTH)?
DATE_TRUNC, almost always. EXTRACT(MONTH) returns 1 for every January in every year, so January 2024 and January 2025 collapse into a single group. DATE_TRUNC keeps the year, so each month of each year stays its own group and sorts in time order.
Why are my months sorting in the wrong order?
Usually because the query groups or sorts on a formatted string such as to_char(order_date, 'Mon YYYY'). Strings sort alphabetically, so April comes before February and February before January. Group and order on the DATE_TRUNC value, and format it only in the final SELECT.
Can I GROUP BY a column alias in PostgreSQL?
Yes. PostgreSQL accepts the alias from the SELECT list, or its position such as GROUP BY 1, so you do not have to repeat a long DATE_TRUNC expression in the GROUP BY clause.
How do I include days or months that have no rows?
GROUP BY cannot create them, because it only groups rows that already exist. Join your data to a generated list of every period, known as a date spine, so that each period is present whether or not anything happened in it.
