{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"grouping-by-time","title":"Grouping by day, week and month without lying about the boundaries","url":"https://addaly.com/learn/data-and-sql/grouping-by-time","position":27,"of":66,"minutes":10,"answer":"Group time with half-open ranges, a named time zone and a stated week start, because BETWEEN drops the last day, engines disagree on Monday versus Sunday.","keyIdea":"Group time with half-open ranges, a named time zone and a stated week start, because BETWEEN drops the last day, engines disagree on Monday versus Sunday, and a month is a different length from the last one."},"body":{"format":"markdown","text":"## Most time-series bugs are boundary bugs\n\n\"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.\n\n## Truncating a timestamp to a period\n\nGrouping by month means turning every timestamp into the first moment of its month, then piling:\n\n```sql\nSELECT date_trunc('month', placed_at) AS month,\n       count(*)                       AS orders,\n       sum(amount)                    AS revenue\nFROM orders\nWHERE placed_at >= '2026-01-01' AND placed_at < '2027-01-01'\nGROUP BY 1\nORDER BY 1;\n```\n\n`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.\n\nNotice 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.\n\n## Which day a week starts on\n\nPostgres 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.\n\nISO 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.\n\n## Months are not the same length\n\nFebruary 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.\n\n> Figure: Four decisions hidden inside \"revenue by month\". Written the quick way: BETWEEN '2026-03-01' AND '2026-03-31': everything after midnight on the 31st is gone; extract(week FROM placed_at): three days of January 2027 land in the wrong year's week 1; date_trunc('day', placed_at) on a timestamptz: the boundary is whoever's midnight the server keeps; March is 10.7% above February, on flat daily sales. Written so the boundaries are stated: >= '2026-03-01' AND < '2026-04-01': half-open, and the last day counts; date_trunc('week', ...), showing the week's start date rather than its number; AT TIME ZONE 'Asia/Kolkata' before truncating, and the zone named in the title; Revenue per day, or March against last March, so the lengths match. Every time-grouped query should answer four questions from its text alone: which zone, which day the week starts on, whether the range is half-open, and what happens to a period with no rows. If it cannot, somebody reading the chart will guess, and they will guess differently from you.\n\n## Whose midnight\n\nA `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.\n\n```sql\nSELECT date_trunc('day', placed_at AT TIME ZONE 'Asia/Kolkata') AS local_day,\n       sum(amount)\nFROM orders\nGROUP BY 1;\n```\n\nDecide 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.\n\n## The month with no rows\n\nA 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.\n\n## The habit\n\nEvery 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."},"check":{"question":"A query filters `WHERE placed_at BETWEEN '2026-03-01' AND '2026-03-31'` on a timestamp column and reports March revenue. Finance says the figure is about 3% low. What is the most likely cause?","options":[{"text":"BETWEEN excludes both endpoints, so orders on 1 March and 31 March were dropped from the total.","correct":false,"tempting":"BETWEEN is inclusive at both ends; the problem is what the second endpoint means on a timestamp column, not exclusion."},{"text":"`'2026-03-31'` on a timestamp column means midnight at the start of that day, so every order placed during 31 March is excluded.","correct":true},{"text":"The timestamps are stored in UTC, and BETWEEN cannot apply a time zone, so about a day of orders at the end of the month has shifted into April.","correct":false,"tempting":"A zone shift moves a few evening hours, not a full day, and it would affect the start of the month as much as the end."},{"text":"The planner used an index on `placed_at` and index scans skip rows that arrived after the index was built.","correct":false,"tempting":"Indexes are maintained on every write; an index scan returns the same rows as a table scan."}]},"prev":{"slug":"percentiles-and-distributions","title":"Percentiles: describing a distribution instead of its mean","url":"https://addaly.com/learn/data-and-sql/percentiles-and-distributions"},"next":{"slug":"ratios-and-rates","title":"Rates: a numerator, a denominator, and the same grain for both","url":"https://addaly.com/learn/data-and-sql/ratios-and-rates"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Grouping by day, week and month without lying about the boundaries', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/grouping-by-time (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Grouping by day, week and month without lying about the boundaries","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":3,"slug":"summaries-and-the-piles-behind-them","title":"Summaries, and the piles behind them"},"answer":null,"keyIdea":"Group time with half-open ranges, a named time zone and a stated week start, because BETWEEN drops the last day, engines disagree on Monday versus Sunday, and a month is a different length from the last one.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"left":{"title":"Written the quick way","items":["BETWEEN ’2026-03-01’ AND ’2026-03-31’: everything after midnight on the 31st is gone","extract(week FROM placed_at): three days of January 2027 land in the wrong year’s week 1","date_trunc('day', placed_at) on a timestamptz: the boundary is whoever's midnight the server keeps","March is 10.7% above February, on flat daily sales"]},"type":"compare","right":{"title":"Written so the boundaries are stated","items":[">= '2026-03-01' AND < '2026-04-01': half-open, and the last day counts","date_trunc('week', ...), showing the week's start date rather than its number","AT TIME ZONE ‘Asia/Kolkata’ before truncating, and the zone named in the title","Revenue per day, or March against last March, so the lengths match"]},"title":"Four decisions hidden inside “revenue by month”","caption":"Every time-grouped query should answer four questions from its text alone: which zone, which day the week starts on, whether the range is half-open, and what happens to a period with no rows. If it cannot, somebody reading the chart will guess, and they will guess differently from you."},"alt":"Figure: Four decisions hidden inside “revenue by month”. Written the quick way: BETWEEN ’2026-03-01’ AND ’2026-03-31’: everything after midnight on the 31st is gone; extract(week FROM placed_at): three days of January 2027 land in the wrong year’s week 1; date_trunc('day', placed_at) on a timestamptz: the boundary is whoever's midnight the server keeps; March is 10.7% above February, on flat daily sales. Written so the boundaries are stated: >= '2026-03-01' AND < '2026-04-01': half-open, and the last day counts; date_trunc('week', ...), showing the week's start date rather than its number; AT TIME ZONE ‘Asia/Kolkata’ before truncating, and the zone named in the title; Revenue per day, or March against last March, so the lengths match. Every time-grouped query should answer four questions from its text alone: which zone, which day the week starts on, whether the range is half-open, and what happens to a period with no rows. If it cannot, somebody reading the chart will guess, and they will guess differently from you."}},"blocks":[{"t":"h2","id":"most-time-series-bugs-are-boundary-bugs","runs":[{"text":"Most time-series bugs are boundary bugs"}]},{"t":"p","runs":[{"text":"“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."}]},{"t":"h2","id":"truncating-a-timestamp-to-a-period","runs":[{"text":"Truncating a timestamp to a period"}]},{"t":"p","runs":[{"text":"Grouping by month means turning every timestamp into the first moment of its month, then piling:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT date_trunc('month', placed_at) AS month,\n       count(*)                       AS orders,\n       sum(amount)                    AS revenue\nFROM orders\nWHERE placed_at >= '2026-01-01' AND placed_at < '2027-01-01'\nGROUP BY 1\nORDER BY 1;"},{"t":"p","runs":[{"text":"date_trunc","code":true},{"text":" works in Postgres and DuckDB and takes "},{"text":"'day'","code":true},{"text":", "},{"text":"'week'","code":true},{"text":", "},{"text":"'month'","code":true},{"text":", "},{"text":"'quarter'","code":true},{"text":", "},{"text":"'year'","code":true},{"text":". SQLite has no such function; use "},{"text":"strftime('%Y-%m', placed_at)","code":true},{"text":", which produces a text key like "},{"text":"2026-03","code":true},{"text":". MySQL has "},{"text":"DATE_FORMAT(placed_at, '%Y-%m')","code":true},{"text":". The text-key versions sort correctly only because the format puts year before month; "},{"text":"'%m/%Y'","code":true},{"text":" would put March 2026 after February 2027."}]},{"t":"p","runs":[{"text":"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. "},{"text":"BETWEEN '2026-01-01' AND '2026-12-31'","code":true},{"text":" looks equivalent and is not, because on a timestamp column "},{"text":"'2026-12-31'","code":true},{"text":" 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."}]},{"t":"h2","id":"which-day-a-week-starts-on","runs":[{"text":"Which day a week starts on"}]},{"t":"p","runs":[{"text":"Postgres and DuckDB start "},{"text":"date_trunc('week', ...)","code":true},{"text":" on Monday, following the ISO standard. Google Sheets and Excel’s "},{"text":"WEEKNUM","code":true},{"text":" start on Sunday by default. MySQL’s "},{"text":"WEEK()","code":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"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 "},{"text":"extract(week ...)","code":true},{"text":" without the ISO year beside it will fold those three days into the wrong year’s week 1. Group by "},{"text":"date_trunc('week', ...)","code":true},{"text":" and show the week’s start date instead of a week number, and the ambiguity disappears."}]},{"t":"h2","id":"months-are-not-the-same-length","runs":[{"text":"Months are not the same length"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"whose-midnight","runs":[{"text":"Whose midnight"}]},{"t":"p","runs":[{"text":"A "},{"text":"timestamptz","code":true},{"text":" 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."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT date_trunc('day', placed_at AT TIME ZONE 'Asia/Kolkata') AS local_day,\n       sum(amount)\nFROM orders\nGROUP BY 1;"},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"the-month-with-no-rows","runs":[{"text":"The month with no rows"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"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."}]}],"check":{"question":[{"text":"A query filters "},{"text":"WHERE placed_at BETWEEN '2026-03-01' AND '2026-03-31'","code":true},{"text":" on a timestamp column and reports March revenue. Finance says the figure is about 3% low. What is the most likely cause?"}],"options":[{"text":[{"text":"BETWEEN excludes both endpoints, so orders on 1 March and 31 March were dropped from the total."}],"correct":false,"tempting":[{"text":"BETWEEN is inclusive at both ends; the problem is what the second endpoint means on a timestamp column, not exclusion."}]},{"text":[{"text":"'2026-03-31'","code":true},{"text":" on a timestamp column means midnight at the start of that day, so every order placed during 31 March is excluded."}],"correct":true,"tempting":null},{"text":[{"text":"The timestamps are stored in UTC, and BETWEEN cannot apply a time zone, so about a day of orders at the end of the month has shifted into April."}],"correct":false,"tempting":[{"text":"A zone shift moves a few evening hours, not a full day, and it would affect the start of the month as much as the end."}]},{"text":[{"text":"The planner used an index on "},{"text":"placed_at","code":true},{"text":" and index scans skip rows that arrived after the index was built."}],"correct":false,"tempting":[{"text":"Indexes are maintained on every write; an index scan returns the same rows as a table scan."}]}],"order":[0,1,2,3],"why":null},"prev":{"slug":"percentiles-and-distributions","title":"Percentiles: describing a distribution instead of its mean","minutes":9},"next":{"slug":"ratios-and-rates","title":"Rates: a numerator, a denominator, and the same grain for both","minutes":9}}}