Probleme mit Zeitstempeln

Promotion-Casts von Timestamp with Time Zone

Die Arbeit mit Zeitzonen in SQL kann mitunter ziemlich verwirrend sein. Beim Filtern auf einen Datumsbereich könnte man beispielsweise die folgende Abfrage versuchen:

SET timezone = 'America/Los_Angeles';

CREATE TABLE times AS
    FROM range('2025-08-30'::TIMESTAMPTZ, '2025-08-31'::TIMESTAMPTZ, INTERVAL 1 HOUR) tbl(t);

FROM times WHERE t <= '2025-08-30';

┌──────────────────────────┐
│            t             │
│ timestamp with time zone │
├──────────────────────────┤
│ 2025-08-30 00:00:00-07   │
└──────────────────────────┘

Wechselt man jedoch die Zeitzone, ändern sich die Ergebnisse der Abfrage:

SET timezone = 'HST';
FROM times WHERE t <= '2025-08-30';

┌──────────────────────────┐
│            t             │
│ timestamp with time zone │
├──────────────────────────┤
│ 2025-08-29 21:00:00-10   │
│ 2025-08-29 22:00:00-10   │
│ 2025-08-29 23:00:00-10   │
│ 2025-08-30 00:00:00-10   │
└──────────────────────────┘

Oder schlimmer:

SET timezone = 'America/New_York';
FROM times WHERE t <= '2025-08-30';

┌──────────────────────────┐
│            t             │
│ timestamp with time zone │
├──────────────────────────┤
│          0 rows          │
└──────────────────────────┘

Diese verwirrenden Ergebnisse entstehen durch die SQL-Casting-Regeln von DATE nach TIMESTAMP WITH TIME ZONE. Dieser Cast muss das Datum auf Mitternacht in der aktuellen Zeitzone anheben.

Sofern Sie die aktuelle Zeitzone nicht für die Anzeige (oder andere zeitliche Binning-Operationen) benötigen, sollten Sie für zeitliche Daten gewöhnliche TIMESTAMPs verwenden. So vermeiden Sie derartige Verwirrungen, und die arithmetischen Operationen sind in der Regel schneller.

Zeitzonen-Performance

DuckDB verwendet die Zeitbibliothek International Components for Unicode für die Zeitzonenunterstützung. Diese Bibliothek hat mehrere Vorteile, darunter die Unterstützung der Sommerzeit über 2037 hinaus. (Hinweis: Pandas liefert nach diesem Jahr falsche Ergebnisse.)

Der Nachteil von ICU ist, dass die Bibliothek nicht besonders performant ist. Ein Workaround besteht darin, eine Kalendertabelle für die modellierten Zeitstempel anzulegen. Modelliert die Anwendung beispielsweise Stromangebot und -nachfrage bis 2100 in stündlicher Auflösung, kann die Kalendertabelle so angelegt werden:

SET timezone = 'Europe/Amsterdam';

CREATE OR REPLACE TABLE hourly AS
    SELECT
        ts,
        year::SMALLINT AS year,
        month::TINYINT AS month,
        day::TINYINT AS day,
        hour::TINYINT AS hour,
    FROM (
        SELECT ts, unnest(date_part(['year', 'month', 'day', 'hour',], ts))
        FROM generate_series(
            '2020-01-01'::DATE::TIMESTAMPTZ,
            '2100-01-01'::DATE::TIMESTAMPTZ,
            INTERVAL 1 HOUR) tbl(ts)
    ) parts;

Diese Tabelle mit rund 700.000 Zeilen können Sie dann mit jeder Zeitstempelspalte joinen, um schnell die zeitlichen Bin-Werte für die betreffende Zeitzone zu erhalten. Die inneren Casts sind nicht erforderlich, führen aber zu einer kleineren Tabelle, weil date_part für alle Teile 64-Bit-Ganzzahlen zurückgibt.

Beachten Sie, dass wir alle Teile mit einem einzigen Aufruf von date_part extrahieren können. Diese Variante der Funktion mit einer Teilliste ist schneller als das einzelne Extrahieren der Teile, weil die zugrunde liegende Binning-Berechnung alle Teile berechnet; das Herausgreifen der in der Liste stehenden Teile vermeidet daher doppelte Aufrufe der langsamen ICU-Funktion.

Beachten Sie außerdem, dass wir die DATE-Cast-Regeln aus dem vorherigen Abschnitt nutzen, um den Kalender auf den Modellbereich zu begrenzen.

Halboffene Intervalle

Ein weiteres subtiles Problem bei der Verwendung von SQL für zeitliche Analysen ist der Operator BETWEEN. Zeitliche Analysen verwenden fast immer halboffene Binning-Intervalle, um Überlappungen an den Enden zu vermeiden. Leider ist der Operator BETWEEN ein abgeschlossenes Intervall (closed-closed):

x BETWEEN begin AND end
-- expands to
begin <= x AND x <= end
-- not
begin <= x AND x < end

Um dieses Problem zu vermeiden, sollten Sie die Vergleichsgrenzen explizit angeben, statt BETWEEN zu verwenden.