Date Parts vs Date Buckets

Concepts covered: Date Parts, Date Truncation, Grouping, Sums & Averages, Common Table Expressions

EXTRACT(part FROM value) pulls a single field out of a date or timestamp and returns it as a number. For 2024-03-15 14:30:45 the year is 2024, the month 3 and the day of the month 15. The hour is 14, and the day of week is 5, because Postgres and DuckDB number the days of the week from Sunday as 0 and this date is a Friday. What separates a part from a bucket is repetition. Month 3 recurs every year, and EXTRACT gives every March the same value. DATE_TRUNC gives each March its own date on the timeline, 2023-03-01 or 2024-03-01. Group by a part and every occurrence of it pools into 1 group; group by a bucket and each period keeps a row of its own.

2 Januaries in 1 row

The trap appears when a trend spans more than 12 months. North's sales now reach back to January 2023, and the request is "North's monthly revenue since January 2023". A candidate who reaches for the month number writes the query in the animation below. The 3 North sales of 2024 from the monthly rollup are still here, joined by 2 sales from early 2023.

SELECT
  EXTRACT(MONTH FROM txn_date) AS month_num,
  SUM(amount) AS revenue
FROM transactions
GROUP BY EXTRACT(MONTH FROM txn_date)
ORDER BY month_num

Transaction 7 on 2023-01-12 extracts to month 1, and so do transactions 1 and 2 from January 2024. The 3 sales fold into a group worth 450. February does the same: 150 from 2023 and 75 from 2024 become 225. The query returns 2 rows where the timeline holds 4 months, and the row reading 450 for January hides the fact the business cares about, that January revenue halved from 300 to 150. Grouping the same rows by DATE_TRUNC('month', txn_date) keeps the years apart:

The fix is to group by DATE_TRUNC('month', txn_date), or by both EXTRACT(YEAR FROM txn_date) and EXTRACT(MONTH FROM txn_date). The 2-column version returns the right rows but hands the consumer 2 integers to recombine before they can sort or plot, and filtering on them takes 2 conditions. The truncated date carries both parts in a single sortable value. The same pooling happens with day of month: grouping by EXTRACT(DAY FROM txn_date) puts the 15th of every month in 1 row, which answers a payday question and nothing else.

When pooling is the point

Parts are the correct tool when the question is about a cycle. "Are Mondays our slowest day?", "What hour do support tickets peak?" and "Which month of the year is typically busiest?" all want every Monday, every 14:00 or every March stacked together, because the cycle is the subject. The query groups by the part, and the care goes into making the comparison fair.

-- Average daily revenue by day of week, last 84 complete days (12 of each weekday)
WITH daily AS (
  SELECT txn_date, SUM(amount) AS day_revenue
  FROM transactions
  WHERE txn_date >= CURRENT_DATE - INTERVAL '84 days'
    AND txn_date <  CURRENT_DATE
  GROUP BY txn_date
)
SELECT
  EXTRACT(DOW FROM txn_date) AS day_of_week,
  COUNT(*) AS days_counted,
  AVG(day_revenue) AS avg_daily_revenue
FROM daily
GROUP BY EXTRACT(DOW FROM txn_date)
ORDER BY day_of_week;

A window of 84 days is 12 weeks exactly, so each weekday appears 12 times in the window and the averages compare like with like. A 30-day window would give 2 weekdays 5 appearances and the other 5 weekdays only 4, and a SUM by weekday would crown whichever days got the extra turn. Averaging the daily totals removes that bias. The days_counted column is the check: it should read 12 on every row. A row reading 11 means a day with no sales at all, which is worth flagging to the interviewer as a gap in the data.

Group by a part (EXTRACT)

• Answers questions about a cycle: weekday, hour of day, month of year
• Pools every occurrence across weeks and years into 1 group
• Returns 7 rows by weekday at most, and no more than 24 by hour or 12 by month, however long the data runs
• Misleads on any trend over time

Group by a bucket (DATE_TRUNC)

• Answers questions about a trend: this month against last
• Keeps each period separate on the timeline
• Adds a row for every new period as the data grows
• Cannot say on its own which weekday is busiest

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.