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.

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