Bei Postgres gibt es oft am Anfang Mißverständnisse bei der Verarbeitung von Zeit-/Datumsangaben.
Intuitiv wird bei Timestamps gedacht, dass sie in der folgenden Repräsentation im Speicher abgelegt werden:
… die Zeitzone ist Bestandteil des Timestamps. So funktioniert es aber nicht!
Alle Timestamps werden als UTC Zeitwerte gespeichert, egal ob mit oder ohne Zeitzone. Der Unterschied eines timestamp with time zone ist der, dass wenn er gespeichert wird, er automatisch von der Zeitzone des Users (wie in der Postgres Connection definiert) nach UTC umgewandelt wird. Genauso wird er beim Abruf durch einen User von UTC in den richtigen Wert der lokalen Zeitzone zurück konvertiert.
Der Vorteil liegt klar auf der Hand: arithmethische Operationen auf Basis der Zeitpunkte werden mit Timestamps aus unterschiedlichen Zeitzonen korrekt ausgeführt. Dies beinhaltet auch Timestamps der gleichen Zeitzone mit unterschiedlicher Sommer-/Winterzeit (“daylight savings time”).
Das folgende Beispiel verdeutlicht den Unterschied zwischen timestamptz und timestamp. Dazu wird eine Tabelle t1 mit zwei Spalten my_timestamptz und my_timestamp erstellt und der gleiche Zeitpunkt in beide Spalten eingefügt.
Als nächstes wird die Zeitzone auf New York, USA, umgestellt. Der Unterschied ist direkt zu erkennen. Bei der Spalte my_timestamptz wird der richtige Zeitpunkt ausgegeben und der Offset ist ersichtlich. Als nächstes wird die Zeitzone auf New York, USA, umgestellt. Der Unterschied ist direkt zu erkennen. Bei der Spalte my_timestamptz wird der richtige Zeitpunkt ausgegeben und der Offset ist ersichtlich.
Und noch einmal mit der Zeitzone für Brisbane in Australien.
Trotzdem gibt es ein paar Anwendungsfälle, die den Datentyp timestamp sinnvoll machen:
- der Treiber unterstützt keine Zeitzonen (ich würde mir bei diesem Fall aber Gedanken machen, ob es nicht Bessere gibt)
- der Quellcode muss auch mit Datenbanken funktionieren, die einen schlechteren Zeitzonen Support haben
- die Verwendung der timestamp Spalte beim Partitionieren als Schlüssel um einen absoluten Wert zu erhalten
- die Datenbank wird ausschließlich (und für immer) nur in einer Zeitzone genutzt
Der Datentyp timestamptz ist bei Oracle und PostgreSQL unterschiedlich. Während Oracle die Timestamp Informationen mit dem Timestamp speichert, speichert Postgres, wie bereits beschrieben, den Timestamp als UTC Wert und zeigt ihn in der gesetzten Zeitzone an. Die Funktionen zeigen also die gleiche Zeit in Postgres und Oracle an, aber die Darstellung unterscheidet sich. Normalerweise ist das kein Problem.
Soll die Zeitzone wirklich mit dem Timestamp gespeichert werden, ist ein zusätzliches Feld notwendig.
Im Beispiel wird eine separate Spalte my_timezone eingefügt, um die Information der Zeitzone separat zu speichern.
Mit Hilfe der Spalte my_timezone kann jetzt auch die Spalte my_timestamp entsprechend korrekt ausgelesen werden. Dazu wird der Operator AT TIME ZONE benutzt.
