Learning center

A JavaScript release that writes a timestamp back drops its microseconds

PostgreSQL stores timestamps to the microsecond, and a JavaScript Date holds milliseconds. When a Node.js release reads a timestamptz that another release wrote and sends it back, the value changes. In our lab, an optimistic lock check on updated_at matched no row, and a write-back changed .123456 to .123. Read the column as text, or compare at millisecond precision, while releases in different languages share the table.

Read, compare, write back#

A Python release writes tickets with full precision. A Node.js release, using node-postgres 8.13 on Node 24, reads them, then saves with a check that nobody changed the ticket in between. We ran this on PostgreSQL 16.15:

INSERT INTO tickets VALUES (1, 'Printer offline', '2026-11-22 10:15:03.123456+00');
const t = (await db.query('SELECT id, title, updated_at FROM tickets WHERE id = 1')).rows[0];
t.updated_at.toISOString();          // '2026-11-22T10:15:03.123Z'

const r = await db.query(
  'UPDATE tickets SET title = $1, updated_at = now() WHERE id = $2 AND updated_at = $3',
  ['Printer offline (2nd floor)', t.id, t.updated_at]);
r.rowCount;                          // 0

Nobody else had touched the ticket. The check failed because the driver turned the column into a Date, the Date kept three of the six fractional digits, and 10:15:03.123 is not 10:15:03.123456. A release that retries on a failed check loops; one that reports a conflict tells the user someone else edited the ticket.

A release that writes the whole row back changes the value silently:

await db.query('UPDATE tickets SET title = $1, updated_at = $2 WHERE id = $3',
               ['Printer offline', t.updated_at, t.id]);
-- updated_at is now 2026-11-22 10:15:03.123+00

Any reconciliation that compares this column between an old and a new table now reports a difference that no user caused.

Keep the value as text#

node-postgres lets you replace the parser for a type. timestamptz is type OID 1184:

const { types } = require('pg');
types.setTypeParser(1184, (value) => value);   // keep timestamptz as text

With that, the same read returned '2026-11-22 10:15:03.123456+00', and the optimistic update returned rowCount 1. Pass the text back as the parameter, and PostgreSQL parses it without loss. The parser setting is process-wide, so code that expects a Date from that column needs to construct one itself.

If you cannot change the reader, make the comparison tolerate the precision:

UPDATE tickets SET ... WHERE id = $2 AND date_trunc('milliseconds', updated_at) = $3;

That accepts any write within the same millisecond as no change, which is a weaker check. Another option is to stop using timestamps as versions, as in add an optimistic lock version that the old release cannot bypass.

Where else it appears#

The same loss happens when a JavaScript service serializes a timestamp to JSON for another service, when it copies rows between tables, and when a backfill written in JavaScript reads and writes the column. Check each path that moves a timestamp through a JavaScript Date. Python's datetime, Java's Instant and Go's time.Time hold microseconds or more, so the asymmetry appears only when releases or services in different languages share the column.

The compatibility test#

Write a row with microseconds from the release that keeps them, then run the JavaScript release's read-and-save path. Pass when the save succeeds and updated_at::text keeps its digits or changes only by the save's own now(). More mixed-release checks are in moving data while two application releases share one database.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading