Postgres AT TIME ZONE 'UTC' tut nicht, was du denkst
Footguns with Postgres "at time zone 'UTC'"
Der Artikel erklärt, warum AT TIME ZONE 'UTC' in Postgres den Datentyp von timestamptz zu timestamp ändert – entgegen der Erwartung. Das führt zu Fallstricken wie fehlgeschlagenen Vergleichen und zeitabhängigen Monatsadditionen. Der Autor zeigt, dass man AT TIME ZONE 'UTC' zweimal anwenden muss, um wieder timestamptz zu erhalten.
In order to fix it, we have to convert timestamp back to timestamptz with another AT TIME ZONE 'UTC'
- heurekamala
Der Text sagt: „Comparing a timestamp and timestamptz will always result in false.“
Das ist falsch. Postgres wandelt den timestamp ganz still und heimlich im Hintergrund in timestamptz um.
Ob es dafür die Zeitzone der Session verwendet.
Ob es true oder false ist, hängt von der TimeZone-Einstellung ab. Das ist schlimmer als „always false“.
In der Produktion mit UTC funktioniert es. Auf dem Laptop eines Entwicklers in Kalifornien funktioniert es nicht.
Gerade getestet:
SET TIME ZONE 'America/Los_Angeles';
SELECT '2026-03-01 00:00:00'::timestamp = '2026-03-01 00:00:00+00'::timestamptz; /* f */
SET TIME ZONE 'UTC';
SELECT '2026-03-01 00:00:00'::timestamp = '2026-03-01 00:00:00+00'::timestamptz; /* t */
Wenn du kannst, verwende Postgres 16.
Das doppelte AT TIME ZONE 'UTC' aus dem Text wird dort nicht benötigt. Dort gibt es date_add mit einer Zeitzone als drittem Argument:
date_add(b.month_start, interval '1 month', 'UTC')
Das addiert den Monat in UTC und es bleibt ein timestamptz.
- ulrikrasmussen
Der SQL-Standard ist leider wirklich furchtbar, was den Umgang mit Zeit angeht. Der Typ `timestamp` ist überhaupt kein Timestamp, weil er keinen eindeutigen Zeitpunkt kodiert, sondern nur ein Datum und eine Uhrzeit speichert, die relativ zu einer Zeitzone interpretiert werden müssen. Er sollte „datetime“ heißen.
Einen Java Instant zwischen einer Datenbank hin und her zu bewegen, ist ebenfalls eine überraschend schwierige Aufgabe, die man richtig machen muss, und es hilft nicht, dass JDBC es völlig falsch handhabt, wenn man seine setTimestamp/getTimestamp-Methoden verwendet. Nicht weil es ein schlechtes Design mit Fußangeln ist, sondern weil die Implementierung schlicht und einfach falsch ist und deine Daten beschädigen wird, wenn du mit Instants arbeitest, deren Kalenderdatum weit genug in der Vergangenheit liegt, weil sie die Legacy-Date/Time-API verwendet, die für Daten in der Vergangenheit auf den gregorianischen Kalender umschaltet.
Der Name `timestamp with time zone` ist ebenfalls irreführend, weil er tatsächlich keine Zeitzone speichert, sondern die Anzahl der Sekunden seit der Epoche wie ein java.time.Instant (allerdings mit einer anderen Auflösung). Der Teil „with time zone“ bezieht sich nur auf das Textformat, in dem du die Werte angibst, das die Zeitzone nach dem Datums-/Zeitteil enthält, um einen Timestamp eindeutig zu identifizieren, aber die Zeitzone wird weggeworfen und nicht gespeichert, nachdem der Wert geparst wurde. Das unterscheidet sich von z. B. `ZonedDateTime` in Java, das tatsächlich den Offset speichert und daher einem Paar aus (Instant, TimeZone) entspricht.
- 1a527dd5
Meine Bibel: https://wiki.postgresql.org/wiki/Don't_Do_This
- Macha
Meine Erfahrung ist (trotz der Empfehlung der Postgres-Entwickler, es zu verwenden), dass timestamptz größtenteils ein nutzloser Datentyp ist, da es nur ein Wrapper um die Konvertierung nach UTC ist.
- Speicherst du vergangene Ereignisse? Speichere sie einfach als UTC. Vielleicht verwendest du timestamptz, damit es das für dich erledigt, aber es ist tatsächlich eine undurchsichtigere Schnittstelle dafür als timestamp
- Speicherst du zukünftige UTC-Zeiten? Großartig, verwende einfach UTC, siehe oben
- Speicherst du zukünftige menschliche Zeiten? Dann ist timestamptz aktiv schädlich, weil es eifrig nach UTC konvertiert, sodass du selbst wenn du rechtzeitig eine aktualisierte tzdb bekommst, wenn das Ereignis fällig ist, nicht weißt, was zum Zeitpunkt des Schreibens passiert ist, und dein Datetime jetzt mehrdeutig ist. Es ist weniger kaputt, einen einfachen timestamp + eine String-Zeitzonenspalte zu verwenden (wenn du danach sortieren musst, vielleicht auch eine denormalisierte _utc-Spalte, mit dem Verständnis, dass du sie neu generieren oder leichte Off-by-one-Fehler akzeptieren musst, wenn du die tzdb aktualisierst, aber zumindest kannst du das tun, wenn du weißt, was der Eingabewert war, anders als bei timestamptz)
- layer8
> Adding a month with + INTERVAL '1 months' is timezone-dependent. […]
Monate zu addieren ist ohnehin nicht wohldefiniert, selbst bei Verwendung von date, für Monatstage > 28. Ich denke, es ist ein Fehler, dass Systeme solche Berechnungen generisch erlauben (im Gegensatz zu Anwendungscode, der domänenspezifische Geschäftsregeln implementiert).