Why date aggregation is its own skill

Stakeholders rarely want a number for one random day. They want revenue per month, active users per week, churn per quarter. The Orders and Sales tables store a timestamp on every row, but grouping by that raw timestamp gives you one near-empty bucket per second. You have to collapse the timestamp into the period you care about before you group. Get the truncation right and the rest is plain GROUP BY.

I treat date aggregation as a two-step move: first reshape the date into a period label, then aggregate by that label. The reshape step is the only part that changes between database engines, so I keep a cheat sheet for each one.

PostgreSQL and BigQuery: DATE_TRUNC

SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM Orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month
1
Truncate to month

The label comes back as the first day of each month, like 2025-01-01.

2
Truncate to quarter

Swap the unit

SELECT DATE_TRUNC('quarter', order_date) AS q, SUM(amount)
FROM Orders
GROUP BY DATE_TRUNC('quarter', order_date)
ORDER BY q

Quarters start on Jan 1, Apr 1, Jul 1, Oct 1.

SELECT DATE_TRUNC('week', order_date) AS week_start, COUNT(order_id)
FROM Orders
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY week_start
3
Truncate to week

The week starts on Monday in Postgres by default.

Query editor showing DATE_TRUNC grouped by month with a revenue bar chart beside it.

MySQL: DATE_FORMAT instead of DATE_TRUNC

4
Build a month key with DATE_FORMAT

MySQL has no DATE_TRUNC, so use

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(amount)
FROM Orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month

The label is a text key like '2025-01' which sorts correctly as text.

SELECT DATE_FORMAT(order_date, '%Y-%u') AS year_week, COUNT(order_id)
FROM Orders
GROUP BY DATE_FORMAT(order_date, '%Y-%u')
ORDER BY year_week
5
Build a year-week key

The %u gives the week number from Monday.

SELECT CONCAT(YEAR(order_date), '-Q', QUARTER(order_date)) AS q, SUM(amount)
FROM Orders
GROUP BY YEAR(order_date), QUARTER(order_date)
ORDER BY q
6
Build a quarter key

This returns labels like '2025-Q1'.

Pro Tip

I recommend grouping by the same expression you put in SELECT, not by an alias, because MySQL and a few other engines reject GROUP BY month when month is an alias. Writing the full DATE_FORMAT twice is ugly but always works.

SQL Server: DATENAME and DATEFROMPARTS

SELECT YEAR(order_date) AS y, MONTH(order_date) AS m, SUM(amount)
FROM Orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY y, m
7
Group by year and month

You group by the two parts separately because SQL Server has no single month-trunc function.

SELECT CONVERT(char(7), order_date, 120) AS month, SUM(amount)
FROM Orders
GROUP BY CONVERT(char(7), order_date, 120)
ORDER BY month
8
Build a sortable month label

Style 120 gives '2025-01' which sorts as text.

Building a clean week-over-week trend

9
Define the week start explicitly

In Postgres use DATE_TRUNC('week', order_date). If your business week starts Sunday, shift it: order_date - INTERVAL '1 day' before truncating, then add it back in the label. A common mistake is assuming every engine starts the week on Monday.

SELECT DATE_TRUNC('week', order_date) AS week_start, COUNT(order_id) AS orders
FROM Orders
WHERE order_date >= '2025-01-01'
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY week_start
10
Count orders per week

This gives a row per week for a trend line.

11
Compare to prior week with LAG

Wrap the weekly query in a CTE and use LAG(orders, 1) OVER (ORDER BY week_start) AS prev_week to compute week-over-week growth without self-joins. This is covered more in our SQL window functions and CTEs guide.

The timezone trap that shifts your periods

If order_date is stored in UTC but your reports are for the US East Coast, a 1 a.m. UTC order becomes the previous day locally. Truncating in UTC can move a sale into the wrong day, week, or even month at the boundary. Convert to the report timezone before you truncate.

12
Convert before truncating

In Postgres,

SELECT DATE_TRUNC('month',
  order_date AT TIME ZONE 'America/New_York') AS month, SUM(amount)
FROM Orders
GROUP BY DATE_TRUNC('month', order_date AT TIME ZONE 'America/New_York')
ORDER BY month

Do the conversion inside the truncate, not after.

Pro Tip

This will break if you truncate first and convert second, because the period boundary is already locked to UTC. In my experience timezone bugs are the number one reason a SQL report disagrees with the billing system by a few percent at month end.

Worked example: quarterly revenue by region

Here is a runnable Postgres query that returns quarterly revenue per region for 2025, sorted so finance can drop it straight into a spreadsheet.

SELECT region, DATE_TRUNC('quarter', order_date) AS quarter, SUM(amount) AS revenue
FROM Orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'
GROUP BY region, DATE_TRUNC('quarter', order_date)
ORDER BY region, quarter;
13
Read the grid

A row with region 'West', quarter '2025-01-01', revenue 482300 means the West region booked $482,300 in Q1. Multiply rows by quarter to build the full pivot table.

Pivot-style grid of region rows against Q1 to Q4 columns with revenue values filled in.

Grouping by period and dimension together

Most time reports need a dimension next to the date, such as revenue per month per region. Add both the truncated date and the dimension to the GROUP BY: GROUP BY DATE_TRUNC('month', order_date), region. The result is one row per month-region cell, ready to drop straight into a pivot table without any post-processing in a spreadsheet.

Keep the date expression identical in

SELECT and GROUP BY, or some engines reject the alias and throw an error. A common mistake is writing SELECT month GROUP BY DATE_TRUNC('month', order_date) and assuming the alias is enough;

On strict engines you must repeat the full expression, which is ugly but always works and avoids a confusing failure in production.

SELECT DATE_TRUNC('month', order_date) AS month, region, SUM(amount) AS revenue
FROM Orders
WHERE order_date >= '2025-01-01'
GROUP BY DATE_TRUNC('month', order_date), region
ORDER BY month, region
14
Build a month by region matrix

This returns the revenue cell for every month-region pair that has at least one order.

Pivot grid with months as rows and regions as columns showing revenue values.

Filling gaps in a date series

DATE_TRUNC only returns periods that have at least one row. If March has no orders, March simply will not appear, which makes a line chart jump from February to April and look broken. To force every month onto the axis, generate a series of months with generate_series in Postgres and LEFT JOIN your aggregated totals onto it.

This matters for finance dashboards where a missing bar reads as a bug even when zero is the correct answer. In my experience a silent gap in the date axis causes more 'the report is broken' tickets than any real calculation error. Generate the calendar first, then join, so zero months show as zero instead of vanishing from the chart entirely.

WITH months AS (
  SELECT generate_series('2025-01-01'::date, '2025-12-01'::date, '1 month') AS m)
SELECT m.m, COALESCE(SUM(o.amount), 0) AS revenue
FROM months m
LEFT JOIN Orders o
ON DATE_TRUNC('month', o.order_date) = m.m
GROUP BY m.m
ORDER BY m.m
15
Left join onto a generated month series

Months with no orders now return a clean zero.

Before you trust a date report, reconcile it against a number you already know. At a previous job our March revenue looked 12 percent low, and the root cause was a timezone shift that moved the last day of the month into April for customers in later time zones. A five-minute check against the billing system's month-end total caught it before the deck went out. I now build every date report with the timezone conversion applied before truncation, plus a control total printed alongside the period breakdown, so any mismatch shows up on the same screen instead of in a stakeholder meeting. That one habit has saved more than one quarterly review from an embarrassing correction, and it costs almost nothing to set up.

Pro Tip

I recommend printing a control total next to every period breakdown so a mismatch shows on the same screen. After the monthly query, run SELECT SUM(amount) FROM Orders and confirm it equals the sum of your month rows. On the Sales table a missing March row from a timezone shift once cut revenue by 12 percent and nobody noticed until the deck was out. I now reconcile against billing before I trust any date report, and I repeat the full DATE_TRUNC expression in GROUP BY instead of the alias.

Next steps and a quick checklist

Pick the truncate function for your engine and save it. Always convert timezones before truncating. Group by the same expression you select. Once monthly and weekly trends are solid, add HAVING to drop periods below a threshold, or use CASE WHEN to tag quarters as peak or off-season. Practice on the Orders table until the period labels come out clean and sorted. Pair this with SQL GROUP BY and HAVING to filter the periods you keep, and use SQL CASE WHEN to tag quarters as peak or off-season. The advanced aggregate guide shows how to pivot these periods into columns.