Dates and time buckets

An instant is only a point on a line: the calendar appears when a person asks for it.

The idea

Store the instant, exactly as it happened. A timestamp has no month, no week, no quarter: those are questions someone asks later. DATE_TRUNC answers them by rounding each instant down to the start of a calendar unit, so every row from March collapses onto the single value 2024-03-01: one value you can group on.

Filtering a range needs the same care. Name the fence posts as start inclusive, next start exclusive. A bare date is midnight, so “up to March 31” quietly means “up to March 31 at 00:00”, and the whole last day walks out of your report without saying goodbye.

Watch it happen

How it works

Two moves, always in this order. First filter the raw instants with a half-open range, so the index on the timestamp column can still be used. Then truncate and group what survived. WHERE runs before GROUP BY, so anything the fence rejects never reaches a bucket at all.

-- monthly counts, done honestly (PostgreSQL)
SELECT date_trunc('month', occurred_at) AS month_start,
       count(*)                         AS events
FROM   events
WHERE  occurred_at >= TIMESTAMP '2024-03-01'   -- start, inclusive
  AND  occurred_at <  TIMESTAMP '2024-04-01'   -- next start, EXCLUSIVE
GROUP  BY 1
ORDER  BY 1;

-- the same question answered on a particular wall clock
SELECT date_trunc('month', occurred_at AT TIME ZONE 'America/New_York') AS month_start,
       count(*) AS events
FROM   events
WHERE  occurred_at >= TIMESTAMP '2024-03-01' AT TIME ZONE 'America/New_York'
  AND  occurred_at <  TIMESTAMP '2024-04-01' AT TIME ZONE 'America/New_York'
GROUP  BY 1
ORDER  BY 1;

The same shape in Python, if you are bucketing in application code:

def month_start(ts):                 # ts must be timezone-aware
    return ts.replace(day=1, hour=0, minute=0, second=0, microsecond=0)

def next_month_start(ts):
    y, m = ts.year, ts.month
    return ts.replace(year=y + m // 12, month=m % 12 + 1, day=1,
                      hour=0, minute=0, second=0, microsecond=0)

lo = month_start(when)               # 2024-03-01 00:00
hi = next_month_start(when)          # 2024-04-01 00:00
march = [e for e in events if lo <= e.occurred_at < hi]   # half-open

Cost

operation	time	space
truncate one instant	O(1)	O(1)
bucket and group n rows	O(n)	O(g), g = distinct buckets
half-open range on an indexed timestamp	O(log n + k), k = rows returned	O(1) extra
same range with the column wrapped: ts::date = …	O(n): full scan	O(1) extra

The trade-off is where you put the arithmetic. Keep the stored column bare on one side of the comparison and compute the boundaries from the literals; the index survives. Wrap the column in a cast or a function and the planner has to touch every row to find out.

Watch out for

• BETWEEN with a bare end date. ts BETWEEN '2024-03-01' AND '2024-03-31' compares against 2024-03-31 00:00:00. Twenty-three hours and fifty-nine minutes of the last day are silently dropped: no error, just a quietly smaller number. Use < '2024-04-01'.
• Patching it with 23:59:59. It looks safe and isn’t: it loses everything in the final second, and it is wrong again the moment the column stores microseconds or you move to a database with different precision. Next start, exclusive is correct at every precision.
• Casting the column to a date. WHERE ts::date = '2024-03-31' returns the right rows but cannot use an index on ts. Write ts >= '2024-03-31' AND ts < '2024-04-01' instead: identical answer, index intact.
• Assuming there is one calendar. date_trunc on a timestamptz truncates in the session time zone, so the same instant lands in different months for different readers. Pick the zone explicitly, and build the range bounds in that same zone. Also remember a month is not 30 × 24 hours: DST days have 23 or 25.
• Missing buckets stay missing. GROUP BY only emits months that have rows, so a quiet April simply vanishes from the chart instead of showing zero. Generate the calendar (generate_series) and LEFT JOIN the counts onto it. And group by the truncated date, not EXTRACT(month FROM ts), which folds every year together.

Worked example

You are asked for “sign-ups in March”. The table holds eight instants; five of them fall in March by UTC wall clock. Written as BETWEEN '2024-03-01' AND '2024-03-31', the right post lands on 2024-03-31 00:00, so the sign-ups at 09:20 and 23:59 on the 31st are rejected before grouping ever happens: the report says 3. Swap in ts >= '2024-03-01' AND ts < '2024-04-01' and the bucket fills to 5. Nothing about the data changed; only the fence did.

Now the finance team wants it on a New York calendar. The sign-up at 2024-03-01 00:30 UTC is 2024-02-29 19:30 in New York, so it belongs to February there. March becomes 4. Both answers are right; they answer different questions. Say which zone the calendar belongs to, truncate in it, and build the bounds in it, and the two numbers stop fighting.

Check yourself