A Postgres driver silently shifted my timestamps by 8 hours — and every test stayed green

libersand — Mon, 17 Aug 2026 14:14:55 +0000

A chunk of the timestamp columns in my database were quietly wrong.

Not corrupted. Not null. Just — eight hours off. The values looked perfectly reasonable in psql. They looked reasonable in the app. They were reasonable, for a timezone I wasn't in.

Every test passed. CI was green. It had been green the whole time.

Here's what was actually happening, and why it's invisible to most of the people who could have warned me.

The setup

Nothing exotic:

Postgres
postgres.js as the driver
Drizzle ORM on top

The schema looked like this — and if you use Drizzle, yours probably does too:

export const user = pgTable('user', {
  id: text('id').primaryKey(),
  createdAt: timestamp('created_at').notNull(),
  updatedAt: timestamp('updated_at').notNull(),
});

That timestamp('created_at') is the important part. In Drizzle, timestamp() with no options generates a Postgres timestamp without time zone column — not timestamptz. The default is the unsafe one.

Two things that are individually fine

One: timestamp without time zone stores no offset.

That isn't a bug, it's the definition of the type. Postgres stores the wall-clock digits you gave it — 2026-08-17 10:00:00 — and nothing else. No zone, no offset. It has no idea whether that's 10am in Shanghai or 10am in Chicago, and it doesn't care.

Two: the driver has to guess on the way back.

When you hand postgres.js a JavaScript Date, it serializes it and sends it over. Postgres receives a value for a timestamp without time zone column, discards the offset, and stores the literal wall clock.

Then you read it back. Postgres hands the driver a bare string with no zone marker. The driver has to turn that into a JS Date, and it has no offset to work with — so it resolves the string against the runtime's local timezone.

Which is not necessarily the timezone the value was written in.

The round trip

My laptop is UTC+8. Trace one value through:

Step	Value
`new Date()` in my app	18:00 Beijing = 10:00 UTC
Driver serializes, Postgres strips the offset	stored as `10:00`
Read back: bare string, no zone	`2026-08-17 10:00:00`
Driver resolves against local time (UTC+8)	10:00 Beijing = 02:00 UTC

Wrote 10:00 UTC. Read 02:00 UTC.

Eight hours. And it compounds — write that value back and you lose another eight.

The number isn't special. It's just my UTC offset. In New York it would be five hours, in the other direction. In London during summer, one.

And in UTC it is zero.

Which is why every test passed

Read that last line again, because it's the whole story.

GitHub Actions runners: UTC
Your Docker container, unless you went out of your way: UTC
Your production server: almost certainly UTC

When the local offset is zero, the write-side and read-side interpretations agree exactly. The bug doesn't produce a small error under UTC. It produces no error at all. This isn't a flaky test or a rare edge case — in a UTC environment the behavior is genuinely, completely correct.

So my test suite wasn't failing to catch a bug. It was running in the one environment where the bug does not exist.

There is exactly one way to trigger it: run code that reads and writes timestamps from a machine whose clock isn't UTC. A developer laptop. Which is precisely what I did.

The expensive version

I needed to backfill a column — copy timestamps from an old table into a new one. Straightforward script: read rows, transform, write rows.

I ran it from my laptop.

Every row it touched moved eight hours. The script was correct. The logic was correct. It would have been fine on the server. It just happened to make a round trip through a JavaScript runtime sitting in UTC+8, and paid the toll on the way through.

The rule I now follow without exception:

A backfill must never round-trip a timestamp through the application layer. Copy it inside the database.

UPDATE new_table n
SET created_at = o.created_at
FROM old_table o
WHERE n.id = o.id;

Postgres moving a value from one column to another cannot get the timezone wrong, because no timezone is ever inferred. There is no JS runtime in the path to guess with.

Fixing it going forward

Use timestamptz. In Drizzle:

createdAt: timestamp('created_at', { withTimezone: true }).notNull(),

timestamptz doesn't actually store a timezone either — that's a common misconception; Postgres normalizes to UTC internally. But the wire format carries an offset in both directions, so the driver never has to guess. The round trip becomes lossless no matter what timezone anything is running in.

Audit what you already have. My schema turned out to be a mix: a handful of newer tables had withTimezone: true and everything older didn't. Nothing in the application code distinguishes them. This finds them:

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type LIKE 'timestamp%'
ORDER BY data_type, table_name;

Set TZ=UTC for your local runtime. This is a band-aid, not a fix — it makes your laptop behave like CI, which hides the bug rather than removing it. But it stops you from making things worse while you migrate.

What I actually took away

I had assumed a green test suite meant the code was correct. What it actually meant was: the code is correct in the environment the tests run in.

Timezone is an ambient property of the machine, not an input to my tests. So is locale. So is filesystem case sensitivity. So is CPU architecture. My CI wasn't wrong — it just isn't a laptop in UTC+8, and it never will be.

If your infrastructure is UTC top to bottom and your team isn't, that gap is where this class of bug lives. You don't close it by writing better tests. You close it by not letting the ambient environment participate in the answer — which, for timestamps, means timestamptz and doing data migrations in SQL.

I'm an indie developer running several small SaaS products solo. This one cost me a day and a data migration, so I figured I'd write it down.

DEV Community: libersand