Dies ist eine HARD -Frage. In der Tat ist es so schwer, dass es scheint, dass der SQL-Standard und die meisten der großen Datenbanken da draußen keinen Hinweis auf ihre Implementierung haben.
Das Konvertieren aller Datumsangaben in UTC ermöglicht einen einfachen Vergleich zwischen Datensätzen, verwirft jedoch die Zeitzoneninformationen, dh Sie können keine Berechnungen mit ihnen durchführen (z. B. 8 Monate zu einem gespeicherten Datum hinzufügen) oder sie in der Zeitzone abrufen gespeichert in. Also ist der naive Ansatz out.
Das Speichern des Zeitzonen-Offsets von UTC zusätzlich zum Timestamp (zB Zeitstempel mit Zeitzone in Postgres) scheint ausreichend zu sein, aber verschiedene Zeitzonen können an einem Punkt im Jahr den gleichen Offset und 6 Monate später einen anderen Offset haben wegen DST. Zum Beispiel könnten Sie New York und Chile jetzt beide an UTC-4 haben (August), aber nach dem 4. November wird New York UTC-5 und Chile (nach dem 2. September) UTC-3 sein. Wenn Sie also nur den Offset speichern, können Sie auch keine genauen Berechnungen durchführen. Wie der obige naive Ansatz verwirft es auch Informationen.
Was passiert, wenn Sie die Zeitzonen-ID (z. B. America / Santiago) mit dem Zeitstempel speichern? Auf diese Weise können Sie zwischen einer chilenischen Datetime und einer New York Datetime unterscheiden. Aber das ist immer noch nicht genug. Wenn Sie ein Ablaufdatum speichern, sagen wir Mitternacht 6 Monate in die Zukunft, und die Regeln für die Sommerzeit ändern sich (wie es leider Politiker gerne tun), dann wird Ihr Zeitstempel falsch sein und der Ablauf könnte um 11 Uhr oder 1 Uhr morgens stattfinden. Was für Ihre Anwendung vielleicht eine große Sache ist oder nicht. Das Verwenden eines Zeitstempels verwirft also auch Informationen.
Es scheint, dass Sie, um wirklich genau zu sein, die lokale Datetime (z. B. mit einem zeitzonenunabhängigen Zeitstempeltyp) mit dem Zeitzonenidentifikator speichern müssen. Um schnellere Vergleiche zu unterstützen, können Sie die UTC-Version zwischenspeichern, bis die von Ihnen verwendete Zeitzonen-DB aktualisiert wird, und den zwischengespeicherten Wert aktualisieren, wenn er sich geändert hat. Das wären also 2 naive Timestamp-Typen plus eine Zeitzonen-Kennung und eine Art externer Cron-Job, der überprüft, ob sich die Zeitzonen-Datenbank geändert hat und die entsprechenden Update-Abfragen für den zwischengespeicherten Zeitstempel ausführt.
Ist das eine genaue Lösung? Oder verpasse ich noch etwas? Könnte es besser gemacht werden?
Ich bin an Lösungen für MySQL, SQL Server, Oracle, PostgreSQL und andere DBMS interessiert, die mit TIMESTAMP WITH TIME ZONE umgehen.
Sie haben das Problem gut zusammengefasst. Leider ist die Antwort das, was du beschrieben hast.
Das richtige Format hängt von der Pragmatik ab, was der Zeitstempel darstellen soll. Es kann im Allgemeinen zwischen vergangenen und zukünftigen Ereignissen aufgeteilt werden (obwohl es Ausnahmen gibt):
Vergangene Ereignisse können und sollten normalerweise als etwas gespeichert werden, das niemals anders interpretiert werden kann. (zB: ein UTC-Zeitstempel mit einer numerischen Zeitzone). Wenn die benannte Zeitzone beibehalten werden soll (um dem Benutzer informativ zu sein), sollte dies getrennt sein.
Zukünftige Ereignisse benötigen die von Ihnen beschriebene Lösung. Lokaler Zeitstempel und benannte Zeitzone. Dies liegt daran, dass Sie die "tatsächliche" Zeit (UTC-Zeit) dieses Ereignisses ändern möchten, wenn sich die Zeitzonenregeln ändern.
Ich würde in Frage stellen, wenn die Zeitzonenumwandlung so ein Overhead ist? Es ist normalerweise ziemlich schnell. Ich würde nur die Schmerzen des Caching durchmachen, wenn Sie einen wirklich signifikanten Leistungseinbruch sehen. Es gibt (wie Sie darauf hingewiesen haben) einige große Operationen, die ein Caching erfordern (z. B. das Sortieren von Milliarden von Zeilen basierend auf der tatsächlichen (UTC) Zeit.
Wenn Sie möchten, dass zukünftige Ereignisse aus Leistungsgründen in UTC zwischengespeichert werden, dann müssen Sie einen Prozess einrichten, um die zwischengespeicherten Werte zu aktualisieren. Abhängig von der Art der Datenbank ist es möglich, dass dies von den Systemadministratoren durchgeführt werden kann, da sich TZ-Regeln selten ändern.
Wenn Ihnen der Offset wichtig ist, sollten Sie den tatsächlichen Offset speichern. Das Speichern des Zeitzonenbezeichners ist nicht dasselbe wie Zeitzonen können und ändern sich im Laufe der Zeit. Indem Sie den Zeitzonen-Offset speichern, können Sie die korrekte Ortszeit zum Zeitpunkt des Ereignisses und nicht die Ortszeit basierend auf dem aktuellen Offset berechnen. Sie können den Zeitzonenidentifikator immer noch speichern, wenn es wichtig ist, zu wissen, welches tatsächliche Zeitzonenereignis als eingetreten gilt.
Denken Sie daran, Zeit ist ein physikalisches Attribut, aber eine Zeitzone ist eine politische.
Wenn Sie in UTC konvertieren, können Sie die Datensätze bestellen und vergleichen Wenn Sie den Namen der Zeitzone hinzufügen, aus der er stammt, können Sie ihn in seinem ursprünglichen tz darstellen und Zeitperioden wie Wochen, Monate usw. (anstelle der verstrichenen Zeit) hinzufügen / subtrahieren.
In Ihrer Frage geben Sie an, dass dies nicht genug ist, da DST möglicherweise geändert wird. DST macht die Berechnung mit Datumsangaben (abgesehen von der verstrichenen Zeit) kompliziert und ziemlich Code-intensiv. Genau wie Sie Code benötigen, um mit Schaltjahren umzugehen, müssen Sie berücksichtigen, ob Sie für eine gegebene Daten / Periode eine DST-Korrektur anwenden müssen oder nicht. Für einige Jahre wird die Antwort für andere ja sein. Siehe diese Wiki-Seite für wie komplex diese Regeln geworden sind.
Das Speichern des Offsets speichert grundsätzlich das Ergebnis dieser Berechnungen. Dieser berechnete Offset ist nur gültig für den gegebenen Zeitpunkt und kann nicht auf wie auf spätere oder frühere Punkte angewendet werden, wie Sie es vorschlagen Ihre Frage. Sie führen die Berechnung zur UTC-Zeit durch und konvertieren dann die resultierende Zeit in die erforderliche Zeitzone basierend auf den Regeln, die zu diesem Zeitpunkt in dieser Zeitzone aktiv sind.
Beachten Sie, dass es vor dem ersten Weltkrieg keine DST gab und dass Datums- / Zeitsysteme in Datenbanken diese Fälle perfekt handhaben.
Ich bin an Lösungen für MySQL, SQL Server, Oracle, PostgreSQL und andere DBMS interessiert, die mit TIMESTAMP WITH TIME ZONE umgehen.
Oracle konvertiert mit sofortiger Zeit in UTC, behält jedoch die Zeitzonen- oder UTC-Verschiebung bei, je nachdem, was Sie übergeben. Oracle (richtig) unterscheidet zwischen der Zeitzone und UTC-Offset und gibt zurück, was Sie an Sie übergeben haben. Dies kostet nur zwei zusätzliche Bytes.
Oracle führt alle Berechnungen in TIMESTAMP WITH TIME ZONE in UTC durch. Das macht keinen Unterschied beim Hinzufügen von Monaten, aber es macht einen Unterschied, wenn Sie Tage hinzufügen, da es keine Sommerzeit gibt. Beachten Sie, dass das Ergebnis einer Berechnung immer ein gültiger Zeitstempel sein muss, z. das Hinzufügen eines Monats zum 31. Januar wird eine Ausnahme in Oracle auslösen, da der 31. Februar nicht existiert.
Tags und Links sql sql-server mysql oracle timezone