SQL Date Buckets and Spines

A bucket is the period a date falls in: its day, week, month, or quarter. Group by the bucket and you get one row per period. A date spine is a list of every period in a range, with no gaps. Join your data to it and the periods with nothing in them come back as 0 instead of disappearing.

The examples use Garden Shop's orders, which run from January 15 to June 14, 2024, unless they say otherwise.

Bucket with date_trunc

date_trunc(part, d) rounds a date down to the start of its period. Every date in March becomes March 1, so grouping by it counts each month once:

select date_trunc('month', order_date)::date as month,
       count(*) as orders
from orders
group by month
order by month

Six rows, from 3 orders in January to 5 in June. The ::date matters: in DuckDB, date_trunc returns a TIMESTAMP even when you give it a DATE, so without the cast every month shows as 2024-01-01 00:00:00.

A bucket is a date, not a month number

month(d) and extract(month from d) return 1 to 12, so grouping by them adds every January together, whatever the year. On SaaS Subscriptions' invoices, which run from April 2023 to December 2025:

-- 12 rows: July is 76 invoices from three different Julys
select month(issued_on) as month, count(*) as invoices
from invoices
group by month
order by month;

-- 33 rows: one per calendar month
select date_trunc('month', issued_on)::date as month, count(*) as invoices
from invoices
group by month
order by month

Group by the month number only when that's the question ("which month of the year is busiest?"). For a trend, group by the truncated date. It also sorts correctly, which a label like strftime(d, '%b %Y') doesn't: 'Apr 2024' sorts before 'Jan 2024'. Format the label last, after sorting by the date.

Weeks

date_trunc('week', d) starts weeks on Monday, the ISO standard. For weeks that start on Sunday, shift the date forward a day, truncate, and shift back:

select date_trunc('week', order_date)::date as monday_week,
       date_trunc('week', order_date + 1)::date - 1 as sunday_week,
       order_date,
       dayname(order_date) as day
from orders
where order_date < date '2024-02-20'
order by order_date

The order on Sunday, February 11 belongs to the week of February 5 when weeks start on Monday, and starts the week of February 11 when they start on Sunday. Pick one rule and say which one in the report: weekly counts change with it.

select year(d) as yr, week(d) as wk, count(*) as days
from (values (date '2024-01-02'), (date '2024-12-30'), (date '2024-12-31')) as t(d)
group by yr, wk;

select date_trunc('week', d)::date as week_start, count(*) as days
from (values (date '2024-01-02'), (date '2024-12-30'), (date '2024-12-31')) as t(d)
group by week_start
order by week_start

Any size of bucket: time_bucket

date_trunc only knows calendar units. For 15-minute slots, 7-day periods that start on a date you choose, or two-week sprints, use time_bucket(width, d, origin). Buckets are counted from the origin:

select time_bucket(interval '15 minutes', timestamp '2024-03-17 10:37:00') as slot,
       time_bucket(interval '7 days', date '2024-03-17', date '2024-01-15') as from_jan_15,
       time_bucket(interval '14 days', date '2024-03-17', date '2024-01-01') as sprint

That gives 10:30, March 11, and March 11. Without an origin, day-based buckets count from Monday, January 3, 2000, so 7-day buckets line up with Monday weeks. PostgreSQL's equivalent is date_bin.

Month boundaries

Filter a month with a half-open range, not BETWEEN the first and last day: on a timestamp, the last day stops at midnight and drops everything after it. See the date range that misses the last day.

Fill the gaps with a date spine

Grouping only returns periods that have rows. Garden Shop has orders in 19 weeks between January 15 and June 14; the 3 weeks with no orders aren't 0, they're missing, and a chart drawn from the result skips straight over them. To get every week, list the weeks first and join the orders to that list:

with weeks as (
  select w::date as week_start
  from range(date '2024-01-15', date '2024-06-17', interval 7 day) as t(w)
)
select w.week_start,
       count(o.order_id) as orders
from weeks as w
left join orders as o
  on date_trunc('week', o.order_date) = w.week_start
group by w.week_start
order by w.week_start

22 weeks, and February 12, March 4, and April 8 show 0. Three details make it work:

• The spine is on the left. A LEFT JOIN keeps every week whether or not an order matches.
• Count a column from the right side. count(o.order_id) skips the NULL an empty week gets. count(*) counts that row, and every empty week shows 1.
• Sums need coalesce. sum(o.amount) of nothing is NULL; wrap it in coalesce(..., 0) to show 0.

Building the spine

Both return TIMESTAMPs, even from dates, so cast with ::date before joining to a date column. To size the spine from the data instead of typing the dates, pass subqueries:

select w::date as week_start
from range(
  (select date_trunc('week', min(order_date)) from orders),
  (select max(order_date) from orders) + 1,
  interval 7 day
) as t(w)

That's the same 22 weeks. The + 1 keeps the last day inside the range, since range stops before its end.

Joining a spine to timestamps

A spine of dates joined with = to a timestamp column only matches events at exactly midnight. On Banking Transactions, where posted_at is a timestamp, that join returns 0 for every day of the first week of January 2024. Truncate the timestamp, or join on a range:

select d.day::date as day,
       count(t.transaction_id) as transactions
from range(date '2024-01-01', date '2024-01-08', interval 1 day) as d(day)
left join transactions as t
  on t.posted_at >= d.day
 and t.posted_at <  d.day + interval 1 day
group by d.day
order by d.day

2, 8, 5, 3, 3, 11, and 7 transactions. on t.posted_at::date = d.day gives the same counts.

Keep filters in the ON clause

A WHERE condition on the joined table runs after the join, and an empty week's NULL fails it, so the zeros vanish again. Put conditions on the right-hand table in the ON clause:

with weeks as (
  select w::date as week_start
  from range(date '2024-01-15', date '2024-06-17', interval 7 day) as t(w)
)
select w.week_start,
       count(o.order_id) as shipped
from weeks as w
left join orders as o
  on date_trunc('week', o.order_date) = w.week_start
 and o.status = 'shipped'
group by w.week_start
order by w.week_start

All 22 weeks, 7 of them with 0 shipped. Move o.status = 'shipped' into a WHERE and those 7 weeks disappear, leaving 15 rows. See why a LEFT JOIN turns into an INNER JOIN.

Zero fill per group

For a row per period and per category, cross join the spine to the list of categories first, then left join the data on both:

with months as (
  select m::date as month
  from range(date '2024-01-01', date '2024-07-01', interval 1 month) as t(m)
),
statuses as (
  select distinct status from orders
)
select m.month, s.status,
       count(o.order_id) as orders
from months as m
cross join statuses as s
left join orders as o
  on date_trunc('month', o.order_date) = m.month
 and o.status = s.status
group by m.month, s.status
order by m.month, s.status

6 months times 4 statuses is 24 rows, including the months where nothing was cancelled. The date spine lesson walks through building one step by step.

How other databases differ