## 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.

```text
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:

```sql
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:

```sql
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:

```sql
-- 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:

```sql
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:

```sql
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