Unit 08.01: Truncating to day, week, and month

Unit ID: SQL-M08-U02 - Estimated active time: 13-16 minutes Objective: group by a time period correctly, and know what your engine's week boundary is.

Truncation collapses a timestamp to a period start

SELECT DATE_TRUNC('day', placed_at) AS day, COUNT(*) AS orders
FROM orders
GROUP BY day
ORDER BY day
LIMIT 3;

DATE_TRUNC returns the first instant of the period, so every timestamp within a day maps to the same value and groups together. CAST(placed_at AS DATE) does the same job for days specifically.

Weeks are where assumptions differ

SELECT DATE_TRUNC('week', placed_at) AS week, COUNT(*) AS orders
FROM orders
GROUP BY week
ORDER BY week;
-- 2026-06-01 | 238
-- 2026-06-08 | 234
-- 2026-06-15 | 231
-- 2026-06-22 | 231
-- 2026-06-29 |  66

Two things to notice.

First, these weeks start on Monday in DuckDB and PostgreSQL. Other systems - and many businesses - start on Sunday. A "weekly revenue" chart can shift by a day's worth of data purely from that choice, so state it.

Second, the final row has 66 orders against roughly 235 for the others. That is not a collapse in demand; it is a partial week - only 29 and 30 June fall inside it. Which is the next unit's subject.

Truncation is not rounding

DATE_TRUNC always moves backwards to the period start. An order at 23:50 on 30 June truncates to 2026-06-30, never forward to 1 July. That is what makes it safe for grouping.

Practice

Produce daily order counts for the first three days of June, and confirm they sum to the same total as a direct count over that range.

SELECT DATE_TRUNC('day', placed_at) AS day, COUNT(*) AS orders
FROM orders
WHERE placed_at >= '2026-06-01' AND placed_at < '2026-06-04'
GROUP BY day
ORDER BY day;

SELECT COUNT(*) FROM orders
WHERE placed_at >= '2026-06-01' AND placed_at < '2026-06-04';
-- 102

Takeaway

DATE_TRUNC moves back to the period start, which is what makes grouping safe. Always state which day your weeks begin on.

---

Mark complete