Recap — Date Bucketing Recipes

DATE_TRUNC('month', order_date) covers maybe 70% of bucketing needs. The other 30% need patterns that aren't built-in.

1) Standard buckets (built-in)

DATE_TRUNC('day', order_date)
DATE_TRUNC('week', order_date)   -- starts on Monday in PostgreSQL
DATE_TRUNC('month', order_date)
DATE_TRUNC('quarter', order_date)
DATE_TRUNC('year', order_date)

Easy. Move on.

2) Fiscal year that doesn't start in January

Many companies run April → March (India), July → June (Australia), or October → September (US federal). Shift the date before truncating:

SELECT
    DATE_TRUNC('year', order_date - INTERVAL '3 months') + INTERVAL '3 months' AS fiscal_year_start
FROM orders;

For an April fiscal year: subtract 3 months, truncate, add 3 back. A March 31 date shifts to Dec 31 (truncates to Jan 1 of that calendar year), then adds 3 months → April 1 of that calendar year. A April 1 date shifts to Jan 1 (truncates to Jan 1), adds 3 → April 1.

Adapt the INTERVAL '3 months' to whatever shift your fiscal needs.

3) ISO weeks — week starts Monday, weeks span year boundaries

SELECT
    EXTRACT(ISOYEAR FROM order_date) AS iso_year,
    EXTRACT(WEEK    FROM order_date) AS iso_week
FROM orders;

ISO weeks are different from calendar weeks: week 1 is the week containing the first Thursday of the year. Jan 1 might be in week 52 of the previous year.

4) Rolling N-day buckets (not aligned to calendar)

"Show me last-7-day metrics regardless of weekday":

SELECT
    order_date,
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_revenue
FROM daily_metrics;

Use ROWS BETWEEN for fixed-row counts; RANGE BETWEEN INTERVAL '6 days' PRECEDING for date-aware sliding (handles missing dates correctly).

5) Custom buckets — quarters that aren't quarters

"Bucket by 13-week periods starting Jan 6":

SELECT
    FLOOR(EXTRACT(DAY FROM (order_date - DATE '2026-01-06')) / 91) AS period_id,
    order_date
FROM orders
WHERE order_date >= DATE '2026-01-06';

91 is days per 13-week period. The FLOOR(... / 91) gives a stable integer per period.

6) Bucketing by relative date — "days since signup"

SELECT
    EXTRACT(DAY FROM (event_time - signup_time)) AS days_since_signup,
    COUNT(*) AS event_count
FROM user_events
GROUP BY 1
ORDER BY 1;

This is days since some user-specific event, not calendar days. Useful for cohort retention, onboarding funnels.

7) "By week, but I want the week to start on Sunday"

In PostgreSQL, DATE_TRUNC('week', ...) starts Monday. To start Sunday:

SELECT DATE_TRUNC('week', order_date + INTERVAL '1 day') - INTERVAL '1 day' AS week_start;

Shift forward, truncate, shift back. Same trick as fiscal year.

Common interview asks

1. "Bucket orders into 5-minute windows" → DATE_TRUNC('minute', order_time) - INTERVAL '1 minute' * (EXTRACT(MINUTE FROM order_time)::int % 5).
2. "Order weeks Sun-Sat" → shift trick above.
3. "Compute day-of-week revenue" → EXTRACT(DOW FROM order_date) then GROUP BY.
4. "Show only business days" → WHERE EXTRACT(DOW FROM order_date) BETWEEN 1 AND 5.

Takeaway

DATE_TRUNC is your 70% case. The other 30% — fiscal years, ISO weeks, custom buckets, business-day filters — all use shift-truncate-shift, EXTRACT, or arithmetic on EXTRACT(DAY FROM ...). Memorize the shift trick; it solves more than you'd expect.