Make create_date and update_date time-zone-aware on all tables #24

New issue

Open

opened 2026-10-03 14:04:54 +00:00 by araemer · 1 comment

araemer commented

2026-10-03 14:04:54 +00:00

Owner

create_date and update_date on every table are TIMESTAMP without a time zone. They come from AbstractEntity (@CreateDateColumn, @UpdateDateColumn), where TypeORM's default column type for PostgreSQL is zone-less. Every explicitly declared time column in the backend is TIMESTAMP WITH TIME ZONE (ticked_at, added_at, the session and passkey expires_at, and items_changed_at from spec 002).

Why it matters

A zone-less timestamp stores only the wall-clock reading. Its meaning depends on configuration:

The database writes now() / CURRENT_TIMESTAMP in its session time zone, and Node reads the value back as its own local time. If the two zones differ, every value is off by a fixed number of hours. The repo sets no explicit time zone for either.
A server move or a time zone change silently shifts the meaning of all stored values.
In a zone with daylight saving, the hour that occurs twice when the clocks go back is ambiguous, so values in it can sort in the wrong order.

These values reach clients as createdAt / updatedAt.

Proposal

Declare both columns in AbstractEntity as timestamp with time zone.
Add a migration that converts both columns on every table, stating explicitly which time zone the existing values are interpreted in (ALTER COLUMN ... TYPE TIMESTAMP WITH TIME ZONE USING ... AT TIME ZONE '<zone>').
Consider setting the time zone explicitly for the database session and for the Node process, so they cannot drift apart.

Found

During the repo architecture step for spec 002 (shopping list overview previews), whose backfill has to read these zone-less columns. Out of scope for 002.

`create_date` and `update_date` on every table are `TIMESTAMP` without a time zone. They come from `AbstractEntity` (`@CreateDateColumn`, `@UpdateDateColumn`), where TypeORM's default column type for PostgreSQL is zone-less. Every explicitly declared time column in the backend is `TIMESTAMP WITH TIME ZONE` (`ticked_at`, `added_at`, the session and passkey `expires_at`, and `items_changed_at` from spec 002). ## Why it matters A zone-less timestamp stores only the wall-clock reading. Its meaning depends on configuration: - The database writes `now()` / `CURRENT_TIMESTAMP` in its session time zone, and Node reads the value back as its own local time. If the two zones differ, every value is off by a fixed number of hours. The repo sets no explicit time zone for either. - A server move or a time zone change silently shifts the meaning of all stored values. - In a zone with daylight saving, the hour that occurs twice when the clocks go back is ambiguous, so values in it can sort in the wrong order. These values reach clients as `createdAt` / `updatedAt`. ## Proposal - Declare both columns in `AbstractEntity` as `timestamp with time zone`. - Add a migration that converts both columns on every table, stating explicitly which time zone the existing values are interpreted in (`ALTER COLUMN ... TYPE TIMESTAMP WITH TIME ZONE USING ... AT TIME ZONE '<zone>'`). - Consider setting the time zone explicitly for the database session and for the Node process, so they cannot drift apart. ## Found During the repo architecture step for spec 002 (shopping list overview previews), whose backfill has to read these zone-less columns. Out of scope for 002.

~~araemer referenced this issue 2026-10-03 14:07:30 +00:00~~

feat(shoppingList) Shopping list overview with item previews (spec 002) #25

araemer commented

2026-10-03 14:07:30 +00:00

Author

Owner

One more finding for this issue, from the spec 002 repo architect:

AbstractDtoEntityMapper.toEntity copies createdAt / updatedAt from the DTO onto the entity. For domains that go through save(), it was not verified whether TypeORM then writes those Node-side values into create_date / update_date. If it does, those rows carry values written in the Node process's zone instead of the database session's zone, and the conversion in the migration would have to account for that.

The shopping list tables are not affected: there, both columns are only written by the database (DEFAULT now() and TypeORM's appended update_date = CURRENT_TIMESTAMP).

The session zone itself is not set anywhere in the repo. It comes from the PostgreSQL server's TimeZone setting in the Puppet configuration; SHOW TimeZone; on stage and production shows it.

One more finding for this issue, from the spec 002 repo architect: `AbstractDtoEntityMapper.toEntity` copies `createdAt` / `updatedAt` from the DTO onto the entity. For domains that go through `save()`, it was not verified whether TypeORM then writes those Node-side values into `create_date` / `update_date`. If it does, those rows carry values written in the Node process's zone instead of the database session's zone, and the conversion in the migration would have to account for that. The shopping list tables are not affected: there, both columns are only written by the database (`DEFAULT now()` and TypeORM's appended `update_date = CURRENT_TIMESTAMP`). The session zone itself is not set anywhere in the repo. It comes from the PostgreSQL server's `TimeZone` setting in the Puppet configuration; `SHOW TimeZone;` on stage and production shows it.

araemer referenced this issue from a commit

2026-10-25 06:07:11 +00:00

feat(migrations) Store create and update times as timestamp with time zone

araemer referenced this issue from a pull request that will close it,

2026-10-25 06:07:22 +00:00

fix(timestamps) Store create and update times as timestamp with time zone #37