the dashboard showed an autopost that had gone out a few seconds ago as happening "in 7 hours".
the cause
Akiko's Postgres has about 72 TIMESTAMP columns with no time zone. node-postgres parses those using the time zone of the Node process, not the database. the database runs in UTC. the server the dashboard runs on is in Los Angeles. so a naive UTC value comes back 7 hours in the future, or 8 in winter.
the read side fix
put the zone back in SQL:
last_posted_at AT TIME ZONE 'UTC' AS last_posted_at
that turns it into a timestamptz, which parses to the right moment on any machine.
i didn't want one global parser because the columns had mixed history. some were written by SQL (NOW() AT TIME ZONE 'utc'), others by JavaScript Date objects, which stored local wall time. one table held both. you can tell the writers apart by fractional seconds. a JS Date gives exactly 3 decimal places and Postgres NOW() gives up to 6.
the write side fix
one line in the bot, pg.defaults.parseInputDatesAsUTC = true, so every Date parameter serializes as UTC from now on. strings i'd built myself are untouched, so nothing gets converted twice.
old rows still carry the old mix, so i check who wrote a column before casting it.
if your database is UTC and your server isn't, grep your schema for timestamp without time zone today :3
Akiko's free to add: https://upload.motzumoto.com/devto
Top comments (0)