2022-01-06

DuckDB-Zeitzonen: Unterstützung für Kalendererweiterungen

Richard Wesley

Zeitzonenunterstützung ist eine häufige Anfrage für temporale Analysen, die Regeln sind aber komplex und etwas willkürlich. Die am besten unterstützte Bibliothek für locale-spezifische Operationen ist International Components for Unicode (ICU). DuckDB bot bereits kollationierte String-Vergleiche über ICU als Erweiterung (um Abhängigkeiten zu vermeiden), und wir haben die bestehenden ICU-Kalender- und Zeitzonenfunktionen jetzt über den neuen Datentyp TIMESTAMP WITH TIME ZONE (kurz TIMESTAMPTZ) an den Hauptcode angebunden. Die ICU-Erweiterung ist im Python-Client von DuckDB vorgebündelt und kann in den übrigen Clients optional installiert werden.

In diesem Beitrag beschreiben wir, wie Zeit in DuckDB funktioniert und welche Zeitzonenfunktionalität ergänzt wurde.

Was ist Zeit?

People assume that time is a strict progression of cause to effect, but actually from a non-linear, non-subjective viewpoint it’s more like a big ball of wibbly wobbly timey wimey stuff.
– Doctor Who: Blink

Zeit in Datenbanken kann sehr verwirrend sein, weil die Art, wie wir über Zeit sprechen, selbst verwirrend ist. Ortszeit, GMT, UTC, Zeitzonen, Schaltjahre, proleptische gregorianische Kalender – das alles sieht nach einem großen Durcheinander aus. Tritt man einen Schritt zurück, ist die Modellierung von Zeit eigentlich recht einfach und lässt sich auf zwei Teile reduzieren: Instanten und Binning.

Instanten

Man hört oft (auch in Dokumentation), dass Datenbankzeit in UTC gespeichert wird. Das ist so ungefähr richtig, genauer ist aber: Datenbanken speichern Instanten. Ein Instant ist ein Punkt in der universellen Zeit, meist als Zählung eines Zeitinkrements von einem festen Zeitpunkt (der Epoche). In DuckDB ist der feste Punkt die Unix-Epoche 1970-01-01 00:00:00 +00:00, das Inkrement sind Mikrosekunden (µs). (Um Verwirrung zu vermeiden, nutzen wir in diesem Beitrag ISO-8601-y-m-d-Notation für Instanten.) Mit anderen Worten: Eine TIMESTAMP-Spalte enthält Instanten.

Es gibt drei weitere temporale Typen in SQL:

• DATE – eine ganzzahlige Zählung von Tagen von einem festen Datum. In DuckDB ist das feste Datum 1970-01-01, wieder in UTC.
• TIME – eine (positive) Zählung von Mikrosekunden bis zu einem einzelnen Tag
• INTERVAL – eine Menge von Feldern zum Zählen von Zeitdifferenzen. In DuckDB zählen Intervalle Monate, Tage und Mikrosekunden. (Monate sind nicht vollständig wohldefiniert, wenn vorhanden, stehen sie für 30 Tage.)

Keiner dieser anderen temporalen Typen außer TIME kann den Modifier WITH TIME ZONE (und das kürzere Suffix TZ) haben, aber um zu verstehen, was dieser Modifier bedeutet, müssen wir zuerst über temporales Binning sprechen.

Temporales Binning

Instanten sind recht einfach – sie sind nur eine Zahl – aber Binning ist der Teil, der Menschen stolpern lässt. Binning ist wahrscheinlich vertraut, wenn Sie mit stetigen Daten gearbeitet haben: Sie zerlegen eine Menge von Werten in Bereiche und bilden jeden Wert auf den Bereich (oder Bin) ab, in den er fällt. Temporales Binning tut das mit Instanten:

Temporale Binning-Systeme werden oft Kalender genannt, diesen Begriff vermeiden wir vorerst, weil Kalender meist mit Daten verbunden werden und temporales Binning auch Regeln für die Uhrzeit umfasst. Diese Zeitregeln heißen Zeitzonen, und sie beeinflussen auch, wo die vom Kalender genutzten Tagesgrenzen fallen. Hier zum Beispiel, wie das Binning für eine zweite Zeitzone an der Epoche aussieht:

Das Verwirrendste am temporalen Binning ist, dass es mehr als einen Weg gibt, Zeit zu binnen, und nicht immer klar ist, welches Binning genutzt werden sollte. Was ich mit „heute“ meine, ist zum Beispiel ein Bin von Instanten, oft bestimmt durch meinen Wohnort. Jeder Instant, der zu meinem „heute“ gehört, kommt in diesen Bin. Beachten Sie, dass ich „heute“ mit „wo ich wohne“ qualifiziert habe, und diese Qualifikation bestimmt, welches Binning-System genutzt wird. „Heute“ könnte aber auch durch „wo die Ereignisse stattfanden“ bestimmt sein, was ein anderes Binning erfordern würde.

Das größte temporale Binning-Problem, auf das die meisten stoßen, tritt bei der Umstellung auf Sommerzeit auf. Dieses Beispiel enthält eine Sommerzeitumstellung, bei der der „Stunden“-Bin zwei Stunden lang ist! Um die zwei Stunden zu unterscheiden, mussten wir einen weiteren Bin mit dem Offset von UTC aufnehmen:

Wie dieses Beispiel zeigt, müssen wir die geltenden Binning-Regeln kennen, um die Instanten korrekt zu binnen. Es zeigt auch, dass wir die eingebauten Binning-Operationen nicht einfach nutzen können, weil sie Sommerzeit nicht verstehen.

Naive Timestamps

Instanten werden manchmal aus einem String-Format mit einem lokalen Binning-System statt einem Instant erzeugt. Dadurch sind die Instanten gegenüber UTC versetzt, was Probleme mit der Sommerzeit verursachen kann. Das nennt man naive Timestamps, und sie können ein Data-Cleaning-Problem darstellen.

Naive Timestamps zu bereinigen erfordert, den Offset für jeden Timestamp zu bestimmen und den Wert dann auf einen Instant zu aktualisieren. Für die meisten Werte geht das mit einem Inequality-Join gegen eine Tabelle mit den korrekten Offsets, die mehrdeutigen Werte müssen vielleicht von Hand korrigiert werden. Es kann auch möglich sein, die mehrdeutigen Werte zu korrigieren, indem man annimmt, dass sie der Reihe nach eingefügt wurden, und mit Window-Funktionen nach „Rückwärtssprüngen“ sucht.

Ein einfacher Weg, diese Situation künftig zu vermeiden, ist, den UTC-Offset zu Nicht-UTC-Strings hinzuzufügen: 2021-07-31 07:20:15 -07:00. Die DuckDB-VARCHAR-Cast-Operation parst diese Offsets korrekt und erzeugt den entsprechenden Instant.

Zeitzonen-Datentypen

Der SQL-Standard definiert temporale Datentypen, qualifiziert durch WITH TIME ZONE. Diese Terminologie ist verwirrend, weil sie impliziert, dass die Zeitzone mit dem Wert gespeichert wird, gemeint ist aber: „binne diesen Wert mit der TimeZone-Einstellung der Sitzung“. Eine TIMESTAMPTZ-Spalte speichert also ebenfalls Instanten, drückt aber einen „Hinweis“ aus, dass ein bestimmtes Binning-System genutzt werden sollte.

Es gibt eine Reihe von Operationen, die auf Instanten ohne Binning-System ausgeführt werden können:

• Vergleichen;
• Sortieren;
• Inkrement- (µs-) Differenz;
• Casten zu und von normalen TIMESTAMPs.

Diese gemeinsamen Operationen sind im DuckDB-Hauptcode umgesetzt, die Binning-Operationen wurden an Erweiterungen wie ICU delegiert.

Ein kleiner Unterschied bei der Anzeige der neuen Typen WITH TIME ZONE gegenüber den älteren Typen ist, dass die neuen Typen mit einem UTC-Offset +00 angezeigt werden. Das macht die Typunterschiede in Kommandozeilenschnittstellen und beim Testen einfach sichtbar. Ein TIMESTAMPTZ korrekt für die Anzeige in einem Locale zu formatieren, erfordert ein Binning-System.

ICU-temporales Binning

DuckDB nutzt bereits eine ICU-Erweiterung zum Kollationieren von Strings für ein bestimmtes Locale, es war also natürlich, sie zu erweitern, um die ICU-Kalender- und Zeitzonenfunktionalität bereitzustellen.

ICU-Zeitzonen

Der erste Schritt zur Unterstützung von Zeitzonen ist, die Einstellung TimeZone zu ergänzen, die angewendet werden soll. DuckDB-Erweiterungen können eigene Einstellungen definieren und validieren, und die ICU-Erweiterung tut das jetzt:

-- Load the extension
-- This is not needed in Python or R, as the extension is already installed
LOAD icu;

-- Show the current time zone. The default is set to ICU's current time zone.
SELECT * FROM duckdb_settings() WHERE name = 'TimeZone';

TimeZone    Europe/Amsterdam    The current time zone   VARCHAR

-- Choose a time zone.
SET TimeZone = 'America/Los_Angeles';

-- Emulate Postgres' time zone table
SELECT name, abbrev, utc_offset
FROM pg_timezone_names()
ORDER BY 1
LIMIT 5;

ACT ACT 09:30:00
AET AET 10:00:00
AGT AGT -03:00:00
ART ART 02:00:00
AST AST -09:00:00

ICU-Funktionen für temporales Binning

Datenbanken wie DuckDB und Postgres bieten üblicherweise einige temporale Binning-Funktionen wie YEAR oder DATE_PART. Diese Funktionen gehören zu einem einzelnen Binning-System für den konventionellen (proleptischen gregorianischen) Kalender und die UTC-Zeitzone. Hinweis: Das Casten zu einem String ist eine Binning-Operation, weil der erzeugte Text Bin-Werte enthält.

Weil Timestamps, die eigenes Binning erfordern, einen anderen Datentyp haben, kann die ICU-Erweiterung zusätzliche Funktionen mit Bindings an TIMESTAMPTZ definieren:

• + – Ein INTERVAL zu einem Timestamp addieren
• - – Ein INTERVAL von einem Timestamp abziehen
• AGE – Ein INTERVAL berechnen, das die Monate/Tage/Mikrosekunden zwischen zwei Timestamps beschreibt (oder einem Timestamp und dem aktuellen Instant).
• DATE_DIFF – Part-Grenzüberschreitungen zwischen zwei Timestamps zählen
• DATE_PART – Einen benannten Timestamp-Teil extrahieren. Dazu gehören die Part-Alias-Funktionen wie YEAR.
• DATE_SUB – Die Anzahl vollständiger Teile zwischen zwei Timestamps zählen
• DATE_TRUNC – Einen Timestamp auf die gegebene Genauigkeit abschneiden
• LAST_DAY – Gibt den letzten Tag des Monats zurück
• MAKE_TIMESTAMPTZ – Konstruiert ein TIMESTAMPTZ aus Teilen, einschließlich eines optionalen letzten Zeitzonenspezifizierers.

Wir haben diese Funktionen nicht für TIMETZ umgesetzt, weil dieser Typ begrenzten Nutzen hat, es wäre aber nicht schwer, das in Zukunft zu ergänzen. Wir haben auch String-Formatierung/Casting nach VARCHAR nicht umgesetzt, weil das Type-Casting-System noch nicht erweiterbar ist und der aktuelle ICU-Build, den wir nutzen, diese Daten nicht einbettet.

ICU-Kalenderunterstützung

ICU kann Binning-Operationen auch für einige nicht-gregorianische Kalender ausführen. Wir haben Unterstützung für diese Kalender über eine Einstellung Calendar und die Tabellenfunktion icu_calendar_names ergänzt:

LOAD icu;

-- Show the current calendar. The default is set to ICU's current locale.
SELECT * FROM duckdb_settings() WHERE name = 'Calendar';

Calendar    gregorian   The current calendar    VARCHAR

-- List the available calendars
SELECT DISTINCT name FROM icu_calendar_names()
ORDER BY 1 DESC LIMIT 5;

roc
persian
japanese
iso8601
islamic-umalqura

-- Choose a calendar
SET Calendar = 'japanese';

-- Extract the current Japanese era number using Tokyo time
SET TimeZone = 'Asia/Tokyo';

SELECT
     era('2019-05-01 00:00:00+10'::TIMESTAMPTZ),
     era('2019-05-01 00:00:00+09'::TIMESTAMPTZ);

235  236

Einschränkungen

ICU hat einige Unterschiede in Verhalten und Darstellung gegenüber der DuckDB-Implementierung. Das sind hoffentlich kleine Punkte, die nur ernste Zeit-Nerds betreffen.

• ICU stellt Instanten als Millisekundenzählungen mit einem DOUBLE dar. Dadurch verliert es weit von der Epoche an Genauigkeit (z. B. um das erste Jahrtausend).
• ICU nutzt den julianischen Kalender für Daten vor der gregorianischen Umstellung am 1582-10-15 statt des proleptischen gregorianischen Kalenders. Daten vor der Umstellung unterscheiden sich daher, obwohl ICU das Datum so angibt, wie es damals tatsächlich geschrieben wurde.
• ICU berechnet Alter über Teilinkremente statt über die Länge des früheren Monats wie DuckDB und Postgres.

Zukünftige Arbeit

Temporale Analyse ist ein großes Gebiet, und obwohl die ICU-Zeitzonenunterstützung ein großer Schritt ist, bleibt viel zu tun. Manche dieser Punkte sind Kernverbesserungen an DuckDB, die allen temporalen Binning-Systemen nützen könnten, manche stellen mehr ICU-Funktionalität bereit. Es gibt auch die Aussicht, andere eigene Binning-Systeme über Erweiterungen zu schreiben.

DuckDB-Features

Hier einige allgemeine Projekte, von denen alle Binning-Systeme profitieren könnten:

• Eine Funktion DATE_ROLL ergänzen, die die ICU-Kalenderoperation roll zum „Rotieren“ um einen enthaltenden Bin nachbildet;
• Casting-Operationen erweiterbar machen, damit Erweiterungen eigene Unterstützung ergänzen können;

ICU-Funktionalität

ICU ist eine sehr reiche Bibliothek mit langer Geschichte, und mit der bestehenden Bibliothek wäre viel möglich:

• Eine allgemeinere Variante von MAKE_TIMESTAPTZ anlegen, die ein STRUCT mit den Teilen entgegennimmt. Das könnte für einige nicht-gregorianische Kalender nützlich sein.
• Die eingebetteten Daten um locale temporale Informationen erweitern (etwa Monatsnamen) und Formatierung (to_char) sowie Parsing (to_timestamp) lokaler Daten unterstützen. Ein Punkt dabei: Die ICU-Datumsformatierungssprache ist ausgefeilter als die von Postgres, daher könnten mehrere Funktionen nötig sein (z. B. icu_to_char);
• Die Binning-Funktionen so erweitern, dass sie Kalender- und Zeitzonenspezifikationen pro Zeile entgegennehmen, um zeilenweise temporale Analysen wie „zu welcher Tageszeit ist das passiert?“ zu unterstützen.

Trennung der Belange

Weil der Zeitzonen-Datentyp im Hauptcode definiert ist, die Kalenderoperationen aber von einer Erweiterung kommen, ist es jetzt möglich, anwendungsspezifische Erweiterungen mit eigener Kalender- und Zeitzonenunterstützung zu schreiben, etwa:

• Finanzielle 4-4-5-Kalender;
• ISO-wochenbasierte Jahre;
• Tabellengesteuerte Kalender;
• Astronomische Kalender mit Schaltsekunden;
• Spaßkalender wie Shire Reckoning und den französischen Revolutionskalender!

Fazit und Feedback

In diesem Beitrag haben wir die neue DuckDB-Zeitzonenfunktionalität beschrieben, umgesetzt über die ICU-Erweiterung. Wir hoffen, dass die bereitgestellte Funktionalität temporale analytische Anwendungen mit Zeitzonen ermöglicht. Wir freuen uns auch auf alle eigenen Kalendererweiterungen, die sich unsere Nutzer ausdenken!

Zuletzt: Wenn Sie Probleme bei der Nutzung unserer Integration haben, öffnen Sie bitte ein Issue im DuckDB-Issue-Tracker!