Hochpräzise Datums-/Zeitangaben in SQLite verwenden: sqlean-time

 (antonz.org)
2 Punkte von GN⁺ 2024-08-16 | 1 Kommentare | Auf WhatsApp teilen
  • Wenn die eingebauten Datumsfunktionen von SQLite nicht ausreichen, fügt sqlean-time als Erweiterung Time- und Duration-Typen sowie Datums-/Zeitfunktionen mit Nanosekundenpräzision hinzu
  • Ein Time-Wert besteht aus den Sekunden seit 0001-01-01 00:00:00 UTC und den Nanosekunden innerhalb der aktuellen Sekunde; bei Speicherung als 13-Byte-BLOB lässt sich ein Bereich von Milliarden Jahren in Vergangenheit und Zukunft abdecken
  • Eine Speicherung als 64-Bit-NUMBER auf Basis der Unix-Epoch ist ebenfalls möglich, aber je kleiner die Einheit, desto kleiner der Bereich; in Nanosekunden ist nur der Zeitraum von 1678 bis 2262 darstellbar
  • Die API umfasst Erzeugung, Feldextraktion, Unix-Time-Konvertierung, Vergleich, Arithmetik, Abschneiden und Runden sowie ISO-8601-Formatierung und -Parsing; Werte werden stets in UTC gespeichert und verarbeitet
  • Kalenderberechnungen setzen den gregorianischen Kalender voraus und berücksichtigen keine Schaltsekunden; für Tage, Monate und Jahre sollte daher time_add_date() statt des Duration-basierten time_add() verwendet werden

Das Zeitmodell von sqlean-time

  • sqlean-time ist eine Erweiterung, die SQLite um hochpräzise Datums-/Zeitverarbeitung erweitert
  • SQLite-Erweiterungen lassen sich durch Herunterladen einer Datei und Ausführen eines einzigen Datenbankbefehls hinzufügen
  • Die Erweiterung arbeitet im Kern mit zwei Arten von Werten
    • Time: ein bestimmter Zeitpunkt
    • Duration: ein Zeitraum

Darstellung von Time und Speicherbereich

  • Time besteht aus einem Paar (seconds, nanoseconds)
    • seconds: eine 64-Bit-Ganzzahl, die die Sekunden seit der Nullzeit 0001-01-01 00:00:00 UTC angibt
    • nanoseconds: der Nanosekundenwert innerhalb der aktuellen Sekunde im Bereich 0-999999999
  • Wenn maximale Flexibilität benötigt wird, kann ein Time-Wert in seiner internen Darstellung als 13-Byte-BLOB gespeichert werden
    • Damit lassen sich Datumswerte Milliarden Jahre in Vergangenheit und Zukunft mit Nanosekundenpräzision darstellen
  • Unterstützt wird auch die Speicherung als 64-Bit-Ganzzahl-NUMBER für Sekunden, Millisekunden, Mikrosekunden oder Nanosekunden seit der Unix-Epoch 1970-01-01 00:00:00 UTC
    • Sekunden: Milliarden Jahre in Vergangenheit und Zukunft mit Sekundenpräzision
    • Millisekunden: 292 Millionen Jahre um 1970 herum mit Millisekundenpräzision
    • Mikrosekunden: darstellbar von -290307 bis 294246
    • Nanosekunden: darstellbar von 1678 bis 2262
  • Time wird immer in UTC gespeichert und verarbeitet
    • Eine Umrechnung mit einem bestimmten Zeitzonen-Offset ist möglich
  • Kalenderberechnungen gehen immer vom gregorianischen Kalender aus
    • Schaltsekunden werden nicht verwendet

Duration und Werterzeugung

  • Duration ist eine 64-Bit-Ganzzahl in Nanosekunden
    • Damit lassen sich Zeiträume von bis zu etwa 290 Jahren ausdrücken
    • Die Speicherung als NUMBER ist möglich
  • Die aktuelle Zeit lässt sich mit time_now() erzeugen
    • Beispiel: time_fmt_iso(time_now()) gibt einen ISO-String wie 2024-08-06T21:22:15.431295000Z zurück
  • Ein bestimmtes Datum bzw. eine bestimmte Uhrzeit wird mit time_date() erzeugt
    • Wird nur das Datum angegeben, ergibt sich Mitternacht UTC
    • Stunde, Minute, Sekunde und Nanosekunden können mit angegeben werden
    • Wenn ein Zeitzonen-Offset übergeben wird, erfolgt die Umrechnung in einen UTC-Zeitpunkt

Extraktion von Datums-/Zeitfeldern

  • Funktionen zur Extraktion einzelner Felder geben Jahr, Monat, Tag, Stunde, Minute, Sekunde, Nanosekunde, Wochentag, Tag des Jahres, ISO-Jahr und ISO-Woche zurück
    • Beispiele: time_get_year(), time_get_month(), time_get_day(), time_get_hour(), time_get_minute(), time_get_second(), time_get_nano()
  • Die generische Funktion time_get() extrahiert Werte über einen Feldnamen als String
    • Unterstützte Beispiele: millennium, century, decade, year, quarter, month, day
    • Beispiele für Zeiteinheiten: hour, minute, second, milli, micro, nano
    • Beispiele für ISO-/Kalenderbezug: isoyear, isoweek, isodow, yearday, weekday
    • Unix-Epoch-Werte lassen sich über epoch abrufen

Unix-Time-Konvertierung

  • Es gibt Funktionen, um aus Unix Time einen Time-Wert zu erzeugen
    • time_unix(seconds)
    • time_unix(seconds, nanoseconds)
    • time_milli(milliseconds)
    • time_micro(microseconds)
    • time_nano(nanoseconds)
  • Es gibt auch Funktionen, um einen Time-Wert wieder in Unix Time zurückzuwandeln
    • time_to_unix()
    • time_to_milli()
    • time_to_micro()
    • time_to_nano()
  • Unix-artige Betriebssysteme speichern Zeit oft als 32-Bit-Sekundenwert, aber time_to_unix() gibt einen 64-Bit-Wert zurück
    • Gültig über einen Bereich von Milliarden Jahren in Vergangenheit und Zukunft
    • time_to_milli() deckt bis zu 292 Millionen Jahre um 1970 herum ab
    • time_to_micro() deckt den Bereich von -290307 bis 294246 ab
    • time_to_nano() deckt den Bereich von 1678 bis 2262 ab

Vergleich und Arithmetik

  • Zeitvergleichsfunktionen bestimmen die Reihenfolge zweier Time-Werte
    • time_after(): gibt zurück, ob die erste Zeit nach der zweiten liegt
    • time_before(): gibt zurück, ob die erste Zeit vor der zweiten liegt
    • time_compare(): gibt 1 zurück, wenn danach, -1 wenn davor und 0 wenn gleich
    • time_equal(): gibt zurück, ob beide Werte denselben Zeitpunkt darstellen
  • time_add() addiert eine Duration zu einem Time-Wert
    • Mit negativer Duration kann subtrahiert werden
    • Duration-Konstanten wie dur_us(), dur_ms(), dur_s(), dur_m(), dur_h() können dabei verwendet werden
  • Zum Addieren von Tagen, Monaten und Jahren sollte nicht time_add(), sondern time_add_date() verwendet werden
    • time_add_date() addiert Jahre, Monate und Tage; mit negativen Werten kann subtrahiert werden
  • time_sub() gibt den Zeitraum zwischen zwei Time-Werten in Nanosekunden zurück
  • time_since() gibt die seit einem angegebenen Zeitpunkt verstrichene Zeit in Nanosekunden zurück
  • time_until() gibt die bis zu einem angegebenen Zeitpunkt verbleibende Dauer in Nanosekunden zurück

Abschneiden und Runden

  • time_trunc() schneidet einen Time-Wert auf die angegebene Feldpräzision ab
    • Unterstützte Beispiele: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, milli, micro
    • Wird etwa 2011-11-18T15:56:35.666777888Z auf hour abgeschnitten, ergibt das 2011-11-18T15:00:00Z
  • Es kann auch auf Vielfache einer angegebenen Duration abgeschnitten werden
    • Beispiele: 12*dur_h(), dur_h(), 30*dur_m(), dur_m(), 30*dur_s(), dur_s()
  • time_round() rundet auf das nächste Vielfache der angegebenen Duration
    • Beispiel: Wird 2011-11-18T15:56:35.666777888Z auf dur_h() gerundet, ergibt das 2011-11-18T16:00:00Z
    • Derselbe Wert auf dur_s() gerundet ergibt 2011-11-18T15:56:36Z

Formatierung und Parsing

  • time_fmt_iso() gibt einen Time-Wert als ISO-8601-String zurück
    • Optional kann ein Zeitzonen-Offset übergeben werden, der vor der Formatierung angewendet wird
    • Werte mit Nanosekunden werden etwa als 2011-11-18T15:56:35.666777888Z dargestellt
    • Mit angegebenem Offset werden sie etwa als 2011-11-18T18:56:35.666777888+03:00 dargestellt
  • time_fmt_datetime(), time_fmt_date(), time_fmt_time() geben jeweils einen datetime-, date- bzw. time-String zurück
    • Optional kann ein Zeitzonen-Offset übergeben werden
  • time_parse() parst formatierte Strings in einen Time-Wert
    • ISO-8601-Strings mit Nanosekunden und Zeitzone
    • ISO-8601-Strings mit Nanosekunden und UTC-Z
    • ISO-8601-Strings mit Zeitzone
    • ISO-8601-UTC-Strings
    • UTC-Datum/Uhrzeit im Format YYYY-MM-DD HH:MM:SS
    • UTC-Datum im Format YYYY-MM-DD
    • UTC-Uhrzeit im Format HH:MM:SS
  • Die von time_parse() unterstützten Layouts bilden eine begrenzte Menge

Duration-Konstanten

  • Es gibt Funktionen, die gebräuchliche Durations als Nanosekunden zurückgeben
    • dur_ns() → 1
    • dur_us() → 1000
    • dur_ms() → 1000000
    • dur_s() → 1000000000
    • dur_m() → 60000000000
    • dur_h() → 3600000000000

Implementierungsbasis und Installation

  • Die Erweiterung ist in C implementiert, basiert in Design und Umsetzung aber stark auf dem Paket time der Go-Standardbibliothek
    • Dieses Paket steht unter der BSD 3-Clause License
  • Die Installation erfolgt durch Herunterladen des latest release und Laden der Erweiterung in der SQLite-CLI
    • Beispiel: .load ./time
    • Nach dem Laden können Abfragen wie select time_now(); verwendet werden

Verwandte Beiträge

1 Kommentare

 
GN⁺ 2024-08-16
Meinungen auf Hacker News

  • Ich frage mich, ob auch Sonderfälle wie die von Jon Skeet berühmt zusammengefassten Zeitzonenänderungen und Diskontinuitäten in der lokalen Uhrzeit behandelt werden.
    https://stackoverflow.com/questions/6841333/why-is-subtracti...
    Computerphile erklärt das ebenfalls sehr gut in einem 10-Minuten-Video.
    https://www.youtube.com/watch?v=-5wpm-gesOY
    Ich habe vor langer Zeit gelernt, keine Datums-/Zeit- oder Kryptografie-Bibliotheken selbst zu bauen. Es gibt endlos viele Edge Cases, bei denen man sich fatal verheddern kann, daher bin ich bei solchen neuen Bibliotheken skeptisch.

    • Diese Bibliothek behandelt das Konzept lokaler Uhrzeit überhaupt nicht. Alles basiert auf UTC-Zeitpunkten; Nutzer können zwar einen Zeitzonen-Offset angeben, aber den schwierigen Teil, den Zeitzonen-Offset zu berechnen, muss der Aufrufer übernehmen.
      Die Dokumentation könnte meiner Meinung nach etwas klarer sein. Der Autor spricht von „time zones“, tatsächlich behandelt die Bibliothek aber nur Zeitzonen-Offsets. Eine Zeitzone ist so etwas wie America/New_York, ein Zeitzonen-Offset ist die Differenz zu UTC. New York hat heute -14400 Sekunden, wird wegen der Sommerzeitumstellung in ein paar Monaten aber -18000 Sekunden haben.

  • Die drei unterschiedlichen Zeitdarstellungen/-größen sind interessant. Mir ist zum Beispiel nicht klar, welcher Anwendungsfall über einen Bereich von Milliarden Jahren hinweg Nanosekunden-Präzision braucht.
    Noch verwirrender ist, dass die Zeitgranularität extrem fein ist, die Nanosekunden-Präzision bei Zeitdauern aber nur einen Bereich von ±290 Jahren abdeckt.

    • Wenn man sich einmal für Nanosekunden-Präzision entscheidet, kann eine 64-Bit-Darstellung nur 584 Jahre abbilden, und das reicht nicht. Man braucht mindestens 2 weitere Bits, um das Jahr 2024 darstellen zu können.
      Wenn man aber schon 2 Bits hinzufügt, gibt es auch keinen Grund, nicht 16 oder 32 Bits hinzuzufügen. Dann deckt man alle ab, von Leuten, die berechnen, wie lange Licht für 30 cm braucht, bis zu Leuten, die das Alter des Universums berechnen.
      Ich stelle mir vor, dass die Designentscheidung ungefähr so verlaufen ist :)
      Natürlich ist es schwierig, Genauigkeit unterhalb einer Sekunde ohne Unterstützung für Schaltsekunden zu bieten, und es ist auch unklar, was Unterstützung für Schaltsekunden vor der menschlichen Zivilisation überhaupt bedeuten soll.
    • Dieser Ansatz hat für mich und Tausende andere Go-Entwickler sehr gut funktioniert. Deshalb habe ich ihn gewählt.

  • Etwas off-topic, aber Datenbanken sollten Einheiten nachverfolgen. Wenn es eine Zeitspalte gibt, sollte man sie zum Beispiel als Duration in float64-Sekunden deklarieren können.
    Dann sollte man SELECT * FROM my_table WHERE duration_s >= 2h schreiben können, und die Datenbank sollte „2h“ automatisch in 7200.0 Sekunden umwandeln und beim Table Scan Werte derselben Einheit vergleichen.
    Vor ein paar Jahren habe ich einmal eine spezialisierte SQL-Datenbank mit einer solchen nativen Einheitenbehandlung gebaut, habe so etwas aber davor oder danach nicht mehr gesehen; es wirkt wie eine Lücke im UI-Ökosystem.
    Das müsste nicht auf Zeit beschränkt sein. Man sollte die ganze Liste von Einheiten behandeln können: Masse, Volumen, Informationsmenge, Temperatur usw. Die Datenbank könnte auch mathematisch unsinnige Ausdrücke wie SELECT 2h + 15kg -- type error! zurückweisen.
    Das würde sehr dabei helfen, Analysefehler früh zu erkennen.

  • Ich finde es wichtig anzugeben, ob vorzeichenbehaftete Ganzzahlen verwendet werden. Beim Lesen der Dokumentation wirkt es so, als könnten sie vorzeichenbehaftet sein, aber vielleicht auch nicht.
    Wenn es vorzeichenbehaftete Ganzzahlen sind, kann es mehrere Bitstrings geben, die dasselbe Datum und dieselbe Uhrzeit darstellen, und das ist nicht gut.

    • Definitiv vorzeichenbehaftet. Es heißt: „Zum Subtrahieren eine negative Duration verwenden.“
      Das Bitmuster ist allerdings eine interne Angelegenheit der Bibliothek. Wenn du im Code einen Bug findest, solltest du ihn natürlich benennen und, wenn möglich, auch einen Fix vorschlagen.
    • Wie ist es möglich, dass es „mehrere Bitstrings gibt, die dasselbe Datum und dieselbe Uhrzeit darstellen“?

  • Es wäre wirklich schön, wenn SQLite3 ein erweiterbares Typsystem hätte.

    • Als jemand, der ein wenig zu PostgreSQL beigetragen hat: Nein, das sollte man nicht tun!!!!
      Ein erweiterbares Typsystem ist für die Performance von Datenbank-Endnutzern furchtbar. Dann kann man beim Parsen und Optimieren von Queries nichts mehr abkürzen. Man muss ständig die Systemkataloge der Query-Engine abfragen, um den Typ jedes Operanden zu prüfen, die richtige Operator-Implementierung zu finden, die passende Index-Operator-Familie/-Klasse zu finden usw.
      Auch Ein- und Ausgabe von Werten laufen über Funktionen, die im Systemkatalog gespeichert sind. Selbst select 1 kann nicht beantwortet werden, ohne in den Systemkatalog zu schauen.
      Es sollte eine geeignete Menge eingebauter Typen und Kombinationsmechanismen wie Structs/JSON geben. So machen es die meisten Datenbanken außer PostgreSQL, und ich bin stark überzeugt, dass das der richtige Weg ist.

  • Eine etwas faule Ask-HN-artige Frage: Was ist eurer Erfahrung nach nützlicher oder wertvoller? Eine Nanosekunden-Darstellung oder die Darstellung von Jahren außerhalb des Nanosekundenbereichs, etwa 1678–2200?
    Ich mache keine echte wissenschaftliche Arbeit, daher scheint mir der Wert von Nanosekunden auf sehr clevere Experimente oder auf die Verfolgung von Finanztransaktionen mit engerem Zeitbereich beschränkt zu sein.
    Dagegen scheint die Fähigkeit, historische Daten darzustellen, häufiger nötig zu sein. Was meint ihr?

    • Historische Daten sind eindeutig wichtiger.
      Schon wenn man die Präzision nur auf 10 Nanosekunden senkt, erhält man einen in der Praxis ausreichenden Bereich.
    • Das ist ein bisschen so, als würde man fragen, was nützlicher ist, ein Hammer oder ein Schraubendreher. Es hängt von der Aufgabe ab.

  • Ich frage mich, warum man nicht wie im Go-Stil Unix-Timestamps als signed int64 in Nanosekunden verwendet. Mit Nanosekunden-Präzision kann man zwar nicht Millionen Jahre abdecken, aber braucht man das wirklich?

    • Bei dieser Präzision und Größe kann man nur den Zeitraum von 1678 bis 2262 abdecken, was die Fähigkeit, historische Daten und Zeiten darzustellen, stark einschränkt.
    • Unix-Timestamps in Nanosekunden zu speichern ist nicht Go-Stil, aber mit dieser Erweiterung kann man es so machen.
      select time_to_nano(time_now());
      -- 1722979335431295000

  • Ich wünschte, Ausdrücke wie „Sekunden seit der Epoch“ würden nur verwendet, wenn genau das gemeint ist.
    Ich frage mich, was select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18)); zurückgeben würde.

    • Warum wünschst du dir das?
      Ein paar plausible Gründe fallen mir ein, aber wirklich entscheidend ist nur: „Welche Epoch?“ In UNIX-basierten Systemen oder Systemen, die deren Verhalten nachahmen wollen, ist das gut definiert. Da du aber nicht sagst, was deine Beschwerde ist, ist es schwer zu widerlegen oder zu rechtfertigen, warum es so ist wie jetzt.
      time_date(1311, 11, 18) ist für die Epoch, die die meisten Computersysteme verwenden, nicht definiert, daher ist jedes Ergebnis möglich: MAX_INT, MIN_INT, 0, ein plausibler Wert, der aber Kalenderreformen nicht berücksichtigt, ein Wert, der durch Umrechnung in eine andere Epoch die genaue Sekundenzahl berechnet, usw. Vor GMT/UTC war alles lokale Uhrzeit, also könnte man auch argumentieren, dass es keine gültige Epoch gibt.
      Natürlich kann man in beide Richtungen argumentieren, ob negative Werte unterstützt werden sollten. Genau 24 Stunden vor 1970-1-1 0:00:00 UTC würde man -86400 erwarten, aber „since“ impliziert ziemlich stark nur positive Werte.
      Andere Leute könnten aus anderen Gründen eine völlig andere Epoch haben, und wenn sich alle innerhalb des verwendeten Bereichs einig sind, ist das auch in Ordnung.
      Oder hattest du einen anderen Einwand?
    • Es heißt: „Wenn das Ergebnis den maximal in Duration speicherbaren Wert überschreitet, wird die maximale Duration zurückgegeben.“