DATE_TRUNC for Calendar Buckets

Concepts covered: Date Truncation, Grouping, Sums & Averages, Current Date

DATE_TRUNC takes a unit and a date or timestamp and returns the start of the period that value falls in. DATE_TRUNC('month', DATE '2024-03-15') returns 2024-03-01, and so does every other date from March 1 through March 31. That many-to-1 mapping is the whole job: 31 different dates become 1 value, so a GROUP BY on that value produces 1 row per month. The unit sets the bucket size, and the table below shows what each unit does to the same timestamp, this lesson's 2024-03-15 14:30:45.

The week row assumes weeks start on Monday, as they do in Postgres and DuckDB, so Friday 2024-03-15 lands in the week of Monday 2024-03-11. If the business counts weeks from Sunday, say so out loud, because every weekly bucket moves by a day.

The monthly rollup

The request is "revenue per region per month for the past year", over a transactions table with 1 row per sale and a DATE column txn_date. Here is the query a strong answer writes, with DATE_TRUNC doing 2 different jobs.

-- One row per (region, month_start), from March 2023 to today
SELECT
  region,
  DATE_TRUNC('month', txn_date) AS month_start,
  SUM(amount) AS monthly_revenue
FROM transactions
WHERE txn_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'
GROUP BY region, DATE_TRUNC('month', txn_date)
ORDER BY region, month_start;

In the SELECT and the GROUP BY, DATE_TRUNC is the bucket: it turns each sale's date into its month. In the WHERE clause it is the anchor: DATE_TRUNC('month', CURRENT_DATE) is 2024-03-01, and subtracting 12 months gives 2023-03-01, so the window opens on the first day of a month. The animation runs the bucketing part over 6 sales from 2 regions, leaving out the WHERE so every row stays in play.

SELECT
  region,
  DATE_TRUNC('month', txn_date) AS month_start,
  SUM(amount) AS monthly_revenue
FROM transactions
GROUP BY region, DATE_TRUNC('month', txn_date)

Watch the 2 North sales from January. Transaction 1 on 2024-01-05 and transaction 2 on 2024-01-28 both truncate to 2024-01-01, so they land in the same group and sum to 150. North's single February sale gives 75. South's January is 1 row of 200, and its 2 February sales, 40 on the 3rd and 60 on the 21st, fold into 100. The 6 rows that go in come out as 4, 1 per region per month. When the interviewer asks for the grain of the output, the answer is the GROUP BY list read aloud: a row for each region and month_start.

Group by the truncated value

A frequent slip is truncating in the SELECT but grouping by the raw column: GROUP BY region, txn_date. The query still runs, because a function of a grouped column is allowed in the SELECT, and it returns 1 row per region per distinct day, each labelled with its month. March can come back as 31 rows that all read 2024-03-01, and a chart drawn from them stacks 31 bars on a single tick. The other slip is building the bucket from text, as in TO_CHAR(txn_date, 'YYYY-MM'). That groups correctly, and 'YYYY-MM' strings happen to sort in date order, but the result is a string, so you can no longer subtract a month from it or compare it with a date. Name formats are worse: TO_CHAR(txn_date, 'Mon YYYY') sorts alphabetically, with 'Apr 2024' ahead of 'Mar 2024'. Keep the bucket a date and format it at the very end, if at all.

Truncate first, then shift

The anchor matters because of what the first bucket contains. Written as txn_date >= CURRENT_DATE - INTERVAL '12 months', the window opens on 2023-03-15, so the March 2023 bucket holds 17 days of sales and plots as a dip that never happened. Truncating first opens the window on 2023-03-01, and every past bucket is a complete month. The last bucket still holds only March 2024 to date, 15 days on 2024-03-15. If the chart should show complete months only, add AND txn_date < DATE_TRUNC('month', CURRENT_DATE), which closes the range at the start of the current month and leaves exactly 12 buckets, March 2023 through February 2024.

About this lesson

This section is part of the Date Arithmetic: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.