Grouping by day, week and month without lying about the boundaries

Most time-series bugs are boundary bugs

“Revenue by month” sounds like the simplest report in the business. Every part of it hides a decision: where a day starts, which day a week starts on, whether February counts for less, and whether 31 March made it into March.

Truncating a timestamp to a period

Grouping by month means turning every timestamp into the first moment of its month, then piling:

SELECT date_trunc('month', placed_at) AS month,
       count(*)                       AS orders,
       sum(amount)                    AS revenue
FROM orders
WHERE placed_at >= '2026-01-01' AND placed_at < '2027-01-01'
GROUP BY 1
ORDER BY 1;

date_trunc works in Postgres and DuckDB and takes 'day', 'week', 'month', 'quarter', 'year'. SQLite has no such function; use strftime('%Y-%m', placed_at), which produces a text key like 2026-03. MySQL has DATE_FORMAT(placed_at, '%Y-%m'). The text-key versions sort correctly only because the format puts year before month; '%m/%Y' would put March 2026 after February 2027.

Notice the WHERE clause. It uses a half-open range: greater than or equal to the start, strictly less than the day after the end. BETWEEN '2026-01-01' AND '2026-12-31' looks equivalent and is not, because on a timestamp column '2026-12-31' means midnight at the start of that day, so every order placed during 31 December is excluded. Half-open ranges are the habit that prevents this, and they also work for any period length without needing the last day’s date.

Which day a week starts on

Postgres and DuckDB start date_trunc('week', ...) on Monday, following the ISO standard. Google Sheets and Excel’s WEEKNUM start on Sunday by default. MySQL’s WEEK() has eight modes and the default is Sunday-start. Two people computing “weekly revenue” from the same table can disagree on every single week, and both be internally consistent.

ISO weeks also cross year boundaries. 1 January 2027 is a Friday, so ISO week 1 of 2027 starts on Monday 4 January, and 1, 2 and 3 January 2027 belong to week 53 of 2026. A report grouped by extract(week ...) without the ISO year beside it will fold those three days into the wrong year’s week 1. Group by date_trunc('week', ...) and show the week’s start date instead of a week number, and the ambiguity disappears.

Months are not the same length

February 2026 has 28 days. March has 31. A business with flat daily sales shows a 10.7% jump from February to March that means nothing. Either report revenue per day for each month, or compare each month to the same month a year earlier, when the length matches. Fridays matter too: a month with five Fridays outsells a month with four for a bar or a cinema, and that difference is calendar, not performance.

Whose midnight

A timestamptz column stores an instant. Which day that instant belongs to depends on a time zone. An order at 01:30 on 1 April in Mumbai was placed at 20:00 UTC on 31 March. Group by UTC and it lands in March; group by local time and it lands in April. Neither is wrong, but only one matches what the shop manager saw.

SELECT date_trunc('day', placed_at AT TIME ZONE 'Asia/Kolkata') AS local_day,
       sum(amount)
FROM orders
GROUP BY 1;

Decide the zone once, write it into the query, and say it in the report title. A business operating in one country uses that country’s zone. A business across several usually reports in UTC and accepts that “yesterday” means something slightly odd to everyone, because the alternative is a report whose days overlap.

The month with no rows

A month in which a branch sold nothing produces no pile and therefore no row, which the earlier lesson on generating a spine solves. Time grouping is the most common place that lesson is needed, because a chart with a missing month draws a line straight across the gap and hides that anything was wrong.

The habit

Every time-grouped query should be able to answer four questions from its text alone: which zone, which day the week starts, whether the range is half-open, and what happens to a period with no rows. If the query cannot answer one of them, somebody reading the chart will guess, and they will guess differently from you.