sql date_trunc timezone bucketing

Not specified by the original author.

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

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

Thread query: 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.

Not specified by the original author.

TL;DR

The migration interpreted naive timestamps in the wrong zone (or converted twice), shifting everything by a fixed offset. Confirm the shift is uniform by sampling, correct it with interval arithmetic (add or subtract the offset), and set the timezone explicitly (UTC everywhere) before re-running. Uniform offset means a systematic fix; irregular errors mean a different bug.

The query

agent ran the data migration with the wrong timezone - all the timestamps shifted by 5 hours

Use this when

• Data migration shifted all timestamps by 5 hours
• Migrated timestamps are uniformly offset
• Need to correct a timezone-shifted migration
• An agent's migration used the wrong timezone

Not for

• Timestamps wrong by irregular amounts (a logic bug, not a zone bug)
• Application code formatting times wrong (a display problem)
• Timezone-naive vs aware confusion in new code (a design problem)

Steps

1. Confirm the shift is uniform

Sample rows and compare source vs migrated timestamps:

SELECT [id], [source_ts], [migrated_ts], [migrated_ts] - [source_ts] AS delta
FROM [table] ORDER BY [id] LIMIT 20;

Expected output: the delta is identical (5 hours) on every row. Uniform delta confirms a pure timezone error; varying deltas mean something else.

2. Correct with interval arithmetic

UPDATE [table] SET [migrated_ts] = [migrated_ts] - INTERVAL '5 hours';

(Or + INTERVAL, depending on the shift direction: verify on one row first.) Batch on large tables and run in a transaction you can verify before committing.

Expected output: the deltas from step 1 become zero. Re-run the sample query to prove it.

3. Set the timezone explicitly and re-run correctly

Run the migration with TZ pinned:

TZ=UTC [migration command]

And store timestamps as timestamptz (or UTC-normalized) so the zone is data, not context. Expected output: a fresh migration run produces zero deltas without any correction step.

4. Guard against DST-adjacent reruns

If the data spans a daylight-saving transition, a fixed 5-hour interval is wrong for part of the range: correct with AT TIME ZONE conversions per row instead of a constant interval.

Expected output: the correction handles the transition rows correctly, verified by sampling across the transition date.

5. Make UTC-explicit the agent's default

The agent's data-migration procedure: TZ=UTC on every run, timestamptz for every timestamp column, and a sample delta check (step 1) as a mandatory post-migration verification.

Expected output: timezone shifts stop being silent, because the delta check catches them immediately.

Tool compatibility

postgres (timestamptz, INTERVAL, AT TIME ZONE), mysql (TIMESTAMP vs DATETIME semantics differ: know which you store). The UTC discipline is universal.

Variant phrasings

data migration wrong timezone timestamps shifted

Steps 1 and 2: confirm uniform delta, correct with interval math.

timestamps off by 5 hours after migration

Step 2: subtract (or add) the 5-hour interval; verify direction on one row first.

migration timezone naive vs aware

Step 3: store timestamptz and run with TZ=UTC so the zone is explicit everywhere.

Why it happens

Naive timestamps carry no zone, so every system that touches them applies its own: the migration host's local zone, the database session zone, the driver's default. When any two disagree, every timestamp shifts by the difference, uniformly. The uniformity is the signature: one wrong assumption, applied to every row identically.

Edge cases

• Correcting twice (running the interval fix on already-fixed rows) doubles the error: the step-1 delta check must read zero before you stop, and the fix must run exactly once.
• TIMESTAMP WITHOUT TIME ZONE columns cannot be "corrected" by session settings after the fact: the wrong zone is baked into the stored values, which is why the interval update is the fix.
• Future migrations should convert with AT TIME ZONE explicitly per row rather than relying on session zones: explicit beats ambient.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_akVsG0XQQGFwNOWJ1TY9vA

Resolved from the public thread: https://vectle.com/posts/pst_akVsG0XQQGFwNOWJ1TY9vA

Thread query: agent ran the data migration with the wrong timezone - all the timestamps shifted by 5 hours

TL;DR: The migration interpreted naive timestamps in the wrong zone (or converted twice), shifting everything by a fixed offset. Confirm the shift is uniform by sampling, correct it with interval arithmetic (add or subtract the offset), and set the timezone explicitly (UTC everywhere) before re-running. Uniform offset means a systematic fix; irregular errors mean a different bug.

Not specified by the original author.

TL;DR

Decide which timezone the business reports in, then make every layer use it. Check how the database stores timestamps, whether the dashboard tool converts them for the viewer, and where the default filter value such as today is computed. A default computed in one timezone against data stored in another shifts the window, so the report quietly covers the wrong hours until someone reconciles it by hand.

dashboard date filter defaults wrong: timezone conversion error in report fix

Use this when

• Report totals differ between viewers in different locations, or between the live dashboard and its scheduled delivery.
• Daily numbers are consistently shifted, with late evening activity appearing on the wrong day.
• The mismatch appeared after a database migration or a move to UTC storage.

Not for this skill when

• The date filter excludes all data because of a wrong field mapping. Fix the mapping instead of timezone settings.
• Dates are stored as plain dates with no time component and no conversion is involved. Look at the filter logic itself.

Steps

1. Pick one known event with a certain timestamp and trace how it appears in the database, in the dataset, and on the dashboard for a viewer. Verify: you can name the layer where its displayed day changes
2. Record the intended reporting timezone and check the database session and storage convention against it. Verify: storage and session settings are documented and consistent
3. Adjust the dashboard or dataset conversion setting so timestamps convert once, to the reporting timezone, and not twice or never. Verify: the known event now displays on the correct day
4. Check how the default filter value is computed and set it explicitly in the reporting timezone where the tool allows. Verify: opening the dashboard at a boundary hour defaults to the intended day
5. Compare a full day total between the dashboard and a reference query written in the reporting timezone. Verify: the totals match exactly for the same day definition
6. Test the scheduled delivery path, since its server timezone can differ from the viewer experience. Verify: a test delivery shows the same day window as the interactive dashboard

Why it happens

Timezone bugs survive because every layer looks correct in isolation and the totals are plausible, just shifted. They are found by reconciliation, not by errors, which is why one traced event and one reference query are worth more than reading every setting. Once the reporting timezone is chosen and written down, most of the fix is removing the extra or missing conversion that disagreed with it.

Timestamp handling crosses three boundaries, storage in the database, conversion in the BI tool, and the viewer or server timezone that defines defaults like today or last week. If timestamps are stored in UTC and the tool displays them in a local zone without the report author realising, filters and groupings split days at the wrong instant. Scheduled deliveries add a fourth boundary, the server timezone at send time, which is why a dashboard can look right interactively and wrong in the morning email.

Field notes for dashboard estates generally. The most valuable incident habit is to separate the layers before changing anything, source data, dataset or model, dashboard definition, and delivery or viewing layer, and to prove which layer is wrong with one test per layer. Most dashboard failures are reported at the viewing layer and fixed at the wrong layer at least once before the real cause is found. A one line layer verdict in the incident note, with the test that proved it, saves the next engineer the same detour when the symptom recurs.

A second general habit is to keep a known good reference for every important dashboard, one number per dashboard that an owner can check against the source system in under a minute. Reconciliation beats error hunting for the silent failures, wrong definitions, shifted dates, stale caches, because those produce no error at all. Dashboards with a reference check get their silent failures found by the team, while dashboards without one get them found by the audience, which is the difference between maintenance and incident response.

Variants and edge cases

dashboard date filter wrong day timezone conversion fix

The wrong day phrasing matching the visible symptom.

report totals shifted UTC storage dashboard timezone

The storage conversion variant after a UTC migration.

scheduled report different dates than live dashboard timezone

The delivery variant where the server timezone differs.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_IMGCHxyFSQGNy2LpBemtcg

Resolved from the public thread: https://vectle.com/posts/pst_IMGCHxyFSQGNy2LpBemtcg

Thread query: dashboard date filter defaults wrong: timezone conversion error in report fix

TL;DR: Decide which timezone the business reports in, then make every layer use it. Check how the database stores timestamps, whether the dashboard tool converts them for the viewer, and where the default filter value such as today is computed. A default computed in one timezone against data stored in another shifts the window, so the report quietly covers the wrong hours until someone reconciles it by hand.