sql date_trunc timezone bucketing

TL;DR

Convert to the target timezone first, then truncate: date_trunc('day', ts AT TIME ZONE 'America/New_York'). Truncating before converting buckets by UTC boundaries, which is wrong for every local-midnight report.

sql date_trunc timezone bucketing

Use this when

• date_trunc buckets do not align with local days/hours
• Reports grouped by "day" split at UTC midnight
• Daylight-saving transitions produce odd buckets

Not for this skill when

• Timestamps fail to parse (to_timestamp nulls)
• You are resampling in pandas
• A window function frame clause errors

Steps

1. See the bug. Truncating a timestamptz buckets in UTC:

SELECT date_trunc('day', created_at) AS day, count(*)
FROM events GROUP BY 1 ORDER BY 1 LIMIT 5;

Expected output: day boundaries at 00:00 UTC. For a US audience, the "day" bucket contains 5pm-5pm Pacific, which is wrong for daily reporting.

2. Convert to the reporting timezone before truncating:

SELECT date_trunc('day', created_at AT TIME ZONE 'America/Los_Angeles') AS day,
       count(*)
FROM events
GROUP BY 1 ORDER BY 1;

Expected output: buckets aligned to Pacific midnight. AT TIME ZONE on a timestamptz yields a local timestamp, which date_trunc then buckets correctly.

3. Handle daylight-saving explicitly. On spring-forward day there are 23 hours, on fall-back 25:

-- the AT TIME ZONE conversion already accounts for DST;
-- verify with a boundary check:
SELECT date_trunc('day', '2026-03-08 02:30:00-08'::timestamptz AT TIME ZONE 'America/Los_Angeles');

Expected output: the nonexistent 2:30am resolves per Postgres rules (shifted forward). The key point: converting first means DST transitions land in the right bucket automatically.

4. For multi-timezone bucketing, bucket per user's timezone:

SELECT date_trunc('day', created_at AT TIME ZONE u.tz) AS local_day, count(*)
FROM events e JOIN users u ON u.id = e.user_id
GROUP BY u.tz, 1;

Expected output: each user's events bucketed in their own midnight. Slower than a single timezone (no single sort order), but correct for per-user analytics.

5. Store and index to make this cheap. If you always report in one timezone, materialize the local timestamp:

ALTER TABLE events ADD COLUMN created_at_pt timestamp
  GENERATED ALWAYS AS (created_at AT TIME ZONE 'America/Los_Angeles') STORED;
CREATE INDEX ON events (date_trunc('day', created_at_pt));

Expected output: the conversion happens once at write time and the index serves the grouping. Do this when the reporting timezone is fixed and the query runs often.

Variant phrasings

postgres date_trunc timezone

AT TIME ZONE before date_trunc (step 2). The order is the entire fix.

sql group by day local timezone

Convert to local time, then truncate, then group. Grouping the raw timestamptz groups by UTC instant.

date_trunc day wrong for daylight saving

If you truncated before converting, DST days bucket wrong. Convert first and the transitions resolve correctly (step 3).

Why it happens

date_trunc operates on the timestamp as given. A timestamptz is an absolute instant; truncating it to 'day' cuts at UTC midnight because that is the only midnight the type knows. Local midnights only exist after conversion to a named timezone.

Edge cases

• date_trunc on plain timestamp (no zone) buckets in whatever zone the value already represents; mixing types is the bug.
• BigQuery's DATE_TRUNC takes a timezone argument directly: DATE_TRUNC(ts, DAY, 'America/Los_Angeles').
• Truncating to 'week' starts Monday in Postgres; ISO week vs US week (Sunday start) needs date_trunc('week', ...) plus an offset.
• Bucketing by month across timezones: convert first, same rule, since month boundaries also shift.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_6-kmJyPKSZX3GlQl3WA7eA