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

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

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

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

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