0Pricing
SQL Interview Prep · Lesson

Truncating and Bucketing Dates

Grouping by week, month, and quarter with DATE_TRUNC and equivalents.

Truncating and Bucketing Dates is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Bucketing Dates Is Asked

"Show revenue by week" or "active users by month" is the bread and butter of analyst interviews. The skill being tested is collapsing precise timestamps into a coarser bucket so rows group together.

The mistake juniors make is extracting just the month number, which merges the same month across different years. The professional answer is truncation: map every timestamp to the start of its period.

  • Week, month, quarter, year buckets
  • DATE_TRUNC and dialect equivalents
  • Grouping correctly so charts line up

DATE_TRUNC: The Core Tool

In PostgreSQL, DATE_TRUNC(unit, ts) zeroes out everything finer than the unit. Truncating to 'month' turns any March timestamp into 2024-03-01 00:00:00.

The return value is still a timestamp, so it sorts chronologically and groups perfectly. This is the single most useful date function for reporting.

SELECT DATE_TRUNC('month', TIMESTAMP '2024-03-17 14:30:00');
-- 2024-03-01 00:00:00

Grouping Revenue by Month

The canonical worked example. Truncate the timestamp to the month, then group and sum. Because the bucket carries the year, January 2023 and January 2024 stay separate.

Ordering by the truncated value gives a clean time series ready for a chart.

SELECT
  DATE_TRUNC('month', order_ts) AS month,
  SUM(amount)                   AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

EXTRACT vs DATE_TRUNC

Interviewers probe this distinction directly. Both pull period info, but they answer different questions.

  • EXTRACT(MONTH FROM ts) returns the number 3 for any March, across all years, useful for seasonality.
  • DATE_TRUNC('month', ts) returns the specific month start, keeping years distinct, useful for time series.

If you group by EXTRACT(MONTH ...) for a monthly trend chart, you will silently blend years together.

-- Seasonality: which month is busiest on average?
SELECT EXTRACT(MONTH FROM order_ts) AS month_num, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

-- Time series: month-by-month trend (years kept separate)
SELECT DATE_TRUNC('month', order_ts) AS month, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

Week Buckets and the Monday Question

Weekly grouping hides a subtlety interviewers enjoy: when does the week start? PostgreSQL's DATE_TRUNC('week', ts) always snaps to Monday (ISO weeks).

If the business wants Sunday-start weeks, you must offset. A common trick is to shift the date back a day, truncate, then shift forward.

-- ISO week (Monday start)
SELECT DATE_TRUNC('week', order_ts) AS iso_week FROM orders;

-- Sunday-start week
SELECT DATE_TRUNC('week', order_ts + INTERVAL '1 day') - INTERVAL '1 day'
  AS sunday_week
FROM orders;

Quarter Buckets

Quarterly reporting is common in finance-adjacent roles. DATE_TRUNC('quarter', ts) maps any timestamp to the first day of its quarter: Jan 1, Apr 1, Jul 1, or Oct 1.

To label the quarter as a number instead, combine EXTRACT(QUARTER ...) with the year.

SELECT
  DATE_TRUNC('quarter', order_ts)                  AS quarter_start,
  EXTRACT(YEAR FROM order_ts) || '-Q'
    || EXTRACT(QUARTER FROM order_ts)              AS quarter_label,
  SUM(amount)                                      AS revenue
FROM orders
GROUP BY 1, 2
ORDER BY 1;

MySQL Has No DATE_TRUNC

A favorite cross-dialect question: "MySQL has no DATE_TRUNC, how do you bucket by month?" The portable answer is to format the date down to the granularity you want.

  • DATE_FORMAT(ts, '%Y-%m-01') gives the month start as text/date.
  • DATE_FORMAT(ts, '%Y-%m') gives a sortable string key like 2024-03.

For week, MySQL offers YEARWEEK() with a mode argument controlling the week start.

-- MySQL month bucket
SELECT DATE_FORMAT(order_ts, '%Y-%m-01') AS month, SUM(amount)
FROM orders
GROUP BY 1
ORDER BY 1;

SQL Server Bucketing

SQL Server historically lacked a direct truncate, so candidates used DATEFROMPARTS or the DATEADD/DATEDIFF idiom. Modern versions (2022+) add DATETRUNC.

The classic idiom "count units since epoch, then add them back" works on every version and is worth knowing.

-- Portable SQL Server month truncation
SELECT DATEADD(month, DATEDIFF(month, 0, order_ts), 0) AS month_start
FROM orders;

-- SQL Server 2022+
SELECT DATETRUNC(month, order_ts) AS month_start FROM orders;

Filling Gaps in a Time Series

Truncation alone drops periods with zero rows: a month with no orders simply will not appear. Interviewers test whether you notice this.

The fix is to generate a complete spine of periods and LEFT JOIN the data onto it. In Postgres, generate_series builds the spine.

SELECT
  cal.month,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(DATE '2024-01-01', DATE '2024-12-01',
                      INTERVAL '1 month') AS cal(month)
LEFT JOIN orders o
  ON DATE_TRUNC('month', o.order_ts) = cal.month
GROUP BY cal.month
ORDER BY cal.month;

Deeper Example: Active Users per Week

Combine bucketing with distinct counting. "Weekly active users" means distinct users per week bucket, a real product-analytics ask.

Truncate the event timestamp to the week, then COUNT(DISTINCT user_id). Mentioning that you would join a week spine to show zero-activity weeks earns extra credit.

SELECT
  DATE_TRUNC('week', event_ts) AS week,
  COUNT(DISTINCT user_id)      AS wau
FROM events
GROUP BY 1
ORDER BY 1;

Bucketing on an Indexed Column

One performance caveat to raise: wrapping the date column in DATE_TRUNC inside a WHERE clause can prevent the planner from using an index on that column.

It is fine in GROUP BY, but for filtering, compare the raw column to computed boundaries instead. We covered this half-open pattern earlier; it applies here too.

-- Avoid in WHERE: DATE_TRUNC('month', order_ts) = '2024-03-01'
-- Prefer:
SELECT * FROM orders
WHERE order_ts >= DATE '2024-03-01'
  AND order_ts <  DATE '2024-04-01';

Quick Check

Pick the right tool for a monthly trend chart that keeps years separate.

Recap: Truncating and Bucketing Dates

What to remember:

  • DATE_TRUNC(unit, ts) maps timestamps to a period start and keeps years distinct, the right tool for time series.
  • EXTRACT returns a bare number, good for seasonality but it merges years.
  • Postgres weeks start Monday; offset if you need Sunday.
  • MySQL uses DATE_FORMAT; older SQL Server uses the DATEADD(DATEDIFF(...)) idiom; 2022+ has DATETRUNC.
  • Use a generated date spine + LEFT JOIN to show empty periods, and keep DATE_TRUNC out of WHERE to preserve index use.

Frequently asked questions

Is the “Truncating and Bucketing Dates” lesson free?

Yes — the full text of “Truncating and Bucketing Dates” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Truncating and Bucketing Dates”?

Grouping by week, month, and quarter with DATE_TRUNC and equivalents. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Truncating and Bucketing Dates” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Date Arithmetic and Intervals
  2. Truncating and Bucketing Dates
  3. Parsing and Formatting Strings
  4. Time Zones and Timestamps
← Back to SQL Interview Prep