Arbeiten mit Zeitzonen: TIMEZONE

Stell dir vor, du hast eine App zum Buchen von Flugtickets. Ein Flug startet in New York um 10:00 Uhr Ortszeit und landet in London um 22:00 Uhr Ortszeit. Wenn du die Zeitzonen nicht beachtest, kann dein Server daraus ein totales Chaos machen und die falsche Ankunftszeit anzeigen.

Zeitzonen sind entweder deine besten Freunde (oder die schlimmsten Feinde, wenn alles schief läuft). Wenn deine User in verschiedenen Ländern sitzen oder du mit Zeitplänen arbeitest, die vom lokalen Zeitpunkt abhängen (zum Beispiel Flugzeiten oder Eventpläne), dann ist das Beachten von Zeitzonen superwichtig.

Typen von Zeitdaten

Wir haben schon besprochen, dass es zwei Datentypen für Zeitstempel gibt:

• TIMESTAMP: Datum und Uhrzeit ohne Zeitzonen-Info.
• TIMESTAMPTZ: Datum und Uhrzeit mit Zeitzonen-Info.

Lass uns das nochmal an einem Beispiel anschauen.

-- Wir erstellen eine Tabelle mit zwei Spalten: TIMESTAMP und TIMESTAMPTZ
CREATE TABLE flight_schedule (
    flight_id SERIAL PRIMARY KEY,
    departure_time TIMESTAMP,
    departure_time_with_tz TIMESTAMPTZ
);

-- Daten einfügen
INSERT INTO flight_schedule (departure_time, departure_time_with_tz)
VALUES
    ('2023-10-25 10:00:00', '2023-10-25 10:00:00+00');

-- Daten prüfen
SELECT * FROM flight_schedule;

Das Ergebnis hängt von der Zeitzone deines Servers ab. Zum Beispiel:

Der entscheidende Unterschied:

• Die Spalte departure_time speichert einfach Datum und Uhrzeit ohne Bezug zu irgendeiner Zeitzone.
• Die Spalte departure_time_with_tz speichert Datum und Uhrzeit zusammen mit der Zeitzonen-Info (+00 in diesem Fall).

Umwandlung von Zeit in verschiedene Zeitzonen

Für die Arbeit mit Zeitzonen in PostgreSQL benutzt man die Funktion AT TIME ZONE.

Umwandlung von UTC in lokale Zeit

Angenommen, wir haben einen Zeitstempel im UTC-Format (Coordinated Universal Time). Wir wollen ihn für einen User anzeigen, der sich in der Zeitzone America/New_York befindet.

SELECT
    '2023-10-25 14:00:00+00'::TIMESTAMPTZ AT TIME ZONE 'America/New_York' AS local_time;

Ergebnis:

AT TIME ZONE wirkt hier wie ein Zauberstab: Es wandelt die Zeit von UTC in die angegebene Zeitzone um.

Umwandlung von lokaler Zeit in UTC

Jetzt das Gegenteil: Wir haben eine Zeit in America/New_York und wollen sie in UTC umwandeln.

SELECT
    '2023-10-25 10:00:00'::TIMESTAMP AT TIME ZONE 'America/New_York' AS utc_time;

Ergebnis:

Beachte, dass das Ergebnis im TIMESTAMPTZ-Format ist, weil es die Zeitzonen-Info (hier UTC) enthält.

Arbeiten mit dem Datentyp TIMESTAMPTZ

Wenn du mit TIMESTAMPTZ arbeitest, berücksichtigt PostgreSQL automatisch die Zeitzone deines Servers (oder die, die du gesetzt hast).

Du kannst die Zeitzone für die aktuelle Session mit folgendem Befehl setzen:

SET TIMEZONE = 'Europe/Istanbul';

Danach werden alle Operationen mit TIMESTAMPTZ in dieser Zeitzone ausgeführt.

Beispiel: Daten einfügen und abfragen

-- Zeitzone setzen
SET TIMEZONE = 'Europe/Istanbul';

-- Daten einfügen
INSERT INTO flight_schedule (departure_time_with_tz)
VALUES ('2023-10-25 10:00:00+00');

-- Daten prüfen
SELECT departure_time_with_tz FROM flight_schedule;

Ergebnis in der Zeitzone Europe/Istanbul:

PostgreSQL wandelt die Zeit von UTC automatisch in die von dir angegebene Zeitzone um.

Praktische Beispiele

Zeitzonen für Zeitpläne berücksichtigen. Angenommen, wir haben eine Tabelle mit Flugplänen, wo jede Zeile die Abflugzeit in UTC speichert. Wir wollen die Abflugzeit für jeden Flug in der lokalen Zeit anzeigen.

SELECT
    flight_id,
    departure_time_with_tz AT TIME ZONE 'America/New_York' AS local_time
FROM flight_schedule;

Vergleich von Zeitdaten aus verschiedenen Zeitzonen. Stell dir vor, wir vergleichen zwei Events, die in verschiedenen Städten passiert sind. PostgreSQL macht das easy, weil es die Daten automatisch auf eine gemeinsame Zeitzone bringt.

SELECT
    '2023-10-25 10:00:00+03'::TIMESTAMPTZ > '2023-10-25 07:00:00+00'::TIMESTAMPTZ AS event_one_later;

Ergebnis:

Der Vergleich ergibt true, weil 10:00+03 gleich 07:00+00 ist.

Tipps und typische Stolperfallen

Mit Zeit zu arbeiten ist tricky. Das geht oft schief:

• Man benutzt TIMESTAMP statt TIMESTAMPTZ und wundert sich dann, warum die Zeit nicht stimmt – weil die Zeitzonen einfach ignoriert werden.
• Man weiß nicht, in welcher Zeitzone der Server läuft, und am Ende werden Daten in einer Zeit eingefügt und in einer anderen gelesen.
• Man vertippt sich beim Namen der Zeitzone bei AT TIME ZONE – und bekommt einen Fehler oder die falsche Zeit.

Damit du nicht auf die Nase fällst:

• Benutz fast immer TIMESTAMPTZ, besonders wenn die Daten von der Zeitzone abhängen könnten.
• Speichere die Zeit in UTC und wandle sie erst beim Anzeigen in die Zeitzone des Users um.
• Wenn du tiefer einsteigen willst, hier ist die offizielle PostgreSQL-Doku zu Zeit und Zeitzonen – echt nützlich!