Der Unterschied der Datentypen timestamp & timestamptz bei PostgreSQL

Database

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:

 

2021-02-19 17:17:25 Europe/Berlin

 

… 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.

 

edb=# SHOW timezone;
   TimeZone
---------------
 Europe/Berlin
(1 Zeile)

edb=# CREATE TABLE t1 (my_timestamptz timestamptz, my_timestamp timestamp);
CREATE TABLE

edb=# INSERT INTO t1 VALUES ('2021-02-19 20:49:15'::timestamptz, '2021-02-19 20:49:15'::timestamp);
INSERT 0 1

edb=# SELECT * FROM t1;
      my_timestamptz       |    my_timestamp
---------------------------+--------------------
 19-FEB-21 20:49:15 +01:00 | 19-FEB-21 20:49:15
(1 Zeile)

 

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.

 

edb=# SET timezone = 'America/New_York';
SET

edb=# SELECT * FROM t1;
      my_timestamptz       |    my_timestamp
---------------------------+--------------------
 19-FEB-21 14:49:15 -05:00 | 19-FEB-21 20:49:15
(1 Zeile)

 

Und noch einmal mit der Zeitzone für Brisbane in Australien.

 

edb=# SET timezone = 'Australia/Brisbane';
SET

edb=# SELECT * FROM t1;
      my_timestamptz       |    my_timestamp
---------------------------+--------------------
 20-FEB-21 05:49:15 +10:00 | 19-FEB-21 20:49:15
(1 Zeile)

 

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.

 

edb=# SET timezone = default;
SET

edb=# SHOW timezone;
   TimeZone
---------------
 Europe/Berlin
(1 Zeile)

edb=# ALTER TABLE t1 ADD COLUMN my_timezone varchar;
ALTER TABLE

edb=# UPDATE t1 SET my_timezone = 'Europe/Berlin' WHERE my_timezone IS NULL;
UPDATE 1

edb=# SELECT * FROM t1;
      my_timestamptz       |    my_timestamp    |  my_timezone
---------------------------+--------------------+---------------
 19-FEB-21 20:49:15 +01:00 | 19-FEB-21 20:49:15 | Europe/Berlin
(1 Zeile)

 

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.

 

edb=# SET timezone = 'America/New_York';
SET

edb=# SELECT my_timestamptz, my_timestamp AT TIME ZONE my_timezone AS my_timestamp FROM t1;
      my_timestamptz       |       my_timestamp
---------------------------+---------------------------
 19-FEB-21 14:49:15 -05:00 | 19-FEB-21 14:49:15 -05:00
(1 Zeile)

edb=# SET timezone = 'Australia/Brisbane';
SET

edb=# SELECT my_timestamptz, my_timestamp AT TIME ZONE my_timezone AS my_timestamp FROM t1;
      my_timestamptz       |       my_timestamp
---------------------------+---------------------------
 20-FEB-21 05:49:15 +10:00 | 20-FEB-21 05:49:15 +10:00
(1 Zeile)

 

 

Dirk Aumueller Autor

Dirk Aumueller arbeitet als Associate Partner für die Proventa AG. Sein technologischer Schwerpunkt liegt bei Datenbankarchitekturen mit PostgreSQL sowie Data Management Lösungen mit Pentaho. Zusätzlich zu seinen Datenbanktransformations-Projekteinsätzen ist er regelmäßig als PostgreSQL Trainer unterwegs und betreut Studenten bei ihren Abschlussarbeiten. Seine fachlichen Erfahrungen erstrecken sich über die Branchen Telco und Financial Services.

Tags