flaxo/ghost

public

Source code of Ghost - On Ghost

store absolute instants: timestamp with time zone everywherea6304c2a month ago51 lines · 2.1 KB

1	# 0018 — Timestamps are `timestamptz`, never bare `timestamp`
2
3	Status: adopted
4
5	## Decision
6
7	Every timestamp column in `packages/db/src/schema` uses
8	`timestamp({ withTimezone: true })`, i.e. Postgres `timestamp with time zone`.
9	No new `timestamp without time zone` column may be added.
10
11	## The problem this exists to solve
12
13	`timestamp without time zone` stores a wall-clock reading with no offset, so
14	its meaning depends on two timezones agreeing: the session that wrote it and
15	the process that reads it. They stopped agreeing. The database sessions run in
16	`Europe/Istanbul` while the API process resolved its zone as UTC, so every date
17	the API returned was shifted +3h: issues "opened in 3 hours", hover cards
18	showing a future UTC time with a correct-looking local conversion. The data was
19	never wrong — the same row read correctly from any process sharing the session
20	zone — which is exactly why it took a three-way comparison (stored wall vs
21	direct read vs API response) to find it.
22
23	`timestamptz` stores an absolute instant. Session zones then only affect
24	presentation of literals, never the stored value, and the pg driver hands back
25	the same instant in every process zone. The class of bug disappears instead of
26	moving.
27
28	## Migration
29
30	`drizzle-kit generate` emits a bare `SET DATA TYPE timestamp with time zone`,
31	which reinterprets existing walls in the migrating session's zone. That is
32	implicitly correct only on some machines, so every statement in `0015` carries
33	an explicit conversion instead:
34
35	```sql
36	ALTER TABLE "issue" ALTER COLUMN "createdAt"
37	SET DATA TYPE timestamp with time zone
38	USING "createdAt" AT TIME ZONE 'Europe/Istanbul';
39	```
40
41	The zone name is the one the old walls were written in. All existing rows
42	were written under it, and Istanbul observes no daylight saving, so each wall
43	names exactly one instant.
44
45	## Consequences
46
47	- Dates are correct in the API, in direct psql-style reads, and on any future
48	server regardless of its system zone.
49	- Any migration that converts timestamp types must carry an explicit
50	`USING ... AT TIME ZONE` clause naming the zone the old walls were written
51	in, so the file stays correct when run from another machine.

store absolute instants: timestamp with time zone everywherea6304c2a month ago51 lines · 2.1 KB

1	# 0018 — Timestamps are `timestamptz`, never bare `timestamp`
2
3	Status: adopted
4
5	## Decision
6
7	Every timestamp column in `packages/db/src/schema` uses
8	`timestamp({ withTimezone: true })`, i.e. Postgres `timestamp with time zone`.
9	No new `timestamp without time zone` column may be added.
10
11	## The problem this exists to solve
12
13	`timestamp without time zone` stores a wall-clock reading with no offset, so
14	its meaning depends on two timezones agreeing: the session that wrote it and
15	the process that reads it. They stopped agreeing. The database sessions run in
16	`Europe/Istanbul` while the API process resolved its zone as UTC, so every date
17	the API returned was shifted +3h: issues "opened in 3 hours", hover cards
18	showing a future UTC time with a correct-looking local conversion. The data was
19	never wrong — the same row read correctly from any process sharing the session
20	zone — which is exactly why it took a three-way comparison (stored wall vs
21	direct read vs API response) to find it.
22
23	`timestamptz` stores an absolute instant. Session zones then only affect
24	presentation of literals, never the stored value, and the pg driver hands back
25	the same instant in every process zone. The class of bug disappears instead of
26	moving.
27
28	## Migration
29
30	`drizzle-kit generate` emits a bare `SET DATA TYPE timestamp with time zone`,
31	which reinterprets existing walls in the migrating session's zone. That is
32	implicitly correct only on some machines, so every statement in `0015` carries
33	an explicit conversion instead:
34
35	```sql
36	ALTER TABLE "issue" ALTER COLUMN "createdAt"
37	SET DATA TYPE timestamp with time zone
38	USING "createdAt" AT TIME ZONE 'Europe/Istanbul';
39	```
40
41	The zone name is the one the old walls were written in. All existing rows
42	were written under it, and Istanbul observes no daylight saving, so each wall
43	names exactly one instant.
44
45	## Consequences
46
47	- Dates are correct in the API, in direct psql-style reads, and on any future
48	server regardless of its system zone.
49	- Any migration that converts timestamp types must carry an explicit
50	`USING ... AT TIME ZONE` clause naming the zone the old walls were written
51	in, so the file stays correct when run from another machine.