In Oracle Datenbanken wird oft der Datentype DATE für Datum und Uhrzeit Verspeicherung genutzt. Solange alle Clients in der gleichen Zeitzone sind, ist das in der Regel auch kein Problem, da alle Benutzer die gleiche Zeit haben. Nur was macht man, wenn man – beispielweise für einen WebShop – die korrekte Uhrzeit (zb: für Bestellungen,…) anzeigen muss?

Natürlich wäre der beste Weg hier einfach auf TIMESTAMP WITH TIMZONE Spalten umzusteigen, nur bringt dies natürlich tiefgreifende Änderungen nicht nur in der Datenbank sondern auch in den Applikationen und Schnittstellen mit sich.

Wenn die Anforderung nur ist, dass man die in DATE gespeicherten Zeitinformationen für den Client korrekt ausgeben möchte, geht das natürlich einfacher.

Testumgebung

Die Datenbank wurde in Mitteleuropa (Zeitzone Europe/Vienna) und somit auch mit Sommerzeit betrieben. Die Ausgabe der Uhrzeit für den Client soll natürlich auch die unterschiedlichen Sommerzeitregelungen berücksichtigen. In dem Beispiel gehen wir davon aus, dass der Client in New York die korrekte Uhrzeit bekommen möchte.

Zuerst müssen wir herausfinden in welcher Zeitzone die Datenbank sich befindet. Dies geht mittels der SQL Funktion DBTIMEZONE.

select dbtimezone from dual;

DBTIME
------
+01:00

Die Datenbank ist +1 Stunde somit ebenfalls für Mitteleuropa (Zeitzone Europe/Vienna) konfiguriert. Das müsste nicht sein, da der Default von Oracle ist, dass die Datenbank mit UTC (+00) erstellt wird.

Die folgende Query nutzt zwei Varianten, um das Datum auf die Uhrzeit des Clients umzurechnen. Damit die Uhrzeit ohne weiters TO_CHAR leesbar ist, nutzen wir die Sessioneinstellung NLS_DATE_FORMAT um das Datum und die Uhrzeit angezeigt zu bekommen.

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

select
    sysdate as now_at_dbtimezone,
    cast(from_tz(cast(sysdate as timestamp),'Europe/Vienna') 
         at time zone 'America/New_York' as date) as correct,
    cast(cast(sysdate as timestamp) 
         at time zone 'America/New_York' as date) as wrong
from dual;

NOW_AT_DBTIMEZONE   CORRECT             WRONG
------------------- ------------------- -------------------
2023-12-21 12:15:43 2023-12-21 06:15:43 2023-12-21 06:15:43

Was sind die Bedeutungen der drei Spalten im Ergebnis?

• Spalte „NOW_AT_DBTIMEZONE“: Der Inhalt des DATE Datentyps
• Spalte „CORRECT“: Die korrekte Umrechnung, die immer ein richtiges Ergebnis liefert
• Spalte „WRONG“: Die einfache Umrechnung, die leider ein falsches Ergebnis liefern kann

Nur warum haben wir in den Spalten „CORRECT“ und „WRONG“ das gleiche Ergebnis stehen? Das liegt daran, dass die Datenbank in der DBTIMEZONE = +01 (Zeitzone Europe/Vienna) betrieben wird.

Wie würde das Ergebnis aussehen, wenn die Datenbank mit dem Default UTC (DBTIMEZONE = +0) laufen würde?

select dbtimezone from dual;

DBTIME
------
+00:00

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

select
    sysdate as now_at_dbtimezone,
    cast(from_tz(cast(sysdate as timestamp),'Europe/Vienna') 
         at time zone 'America/New_York' as date) as correct,
    cast(cast(sysdate as timestamp) 
         at time zone 'America/New_York' as date) as wrong
from dual;

NOW_AT_DBTIMEZONE   CORRECT             WRONG
------------------- ------------------- -------------------
2023-12-21 11:18:55 2023-12-21 05:18:55 2023-12-21 06:18:55

Hier sehen wir, dass in diesem Fall in den Spalten „CORRECT“ und „WRONG“ wirklich etwas anderes steht. Der Grund ist, dass in der einfacheren Variante („WRONG“) Oracle beim cast(sysdate as timestamp) at time zone 'America/New_York' als Basis natürlich die DBTIMEZONE nutzt und nicht weis, dass die DATE Spalten eigentlich die Uhrzeit der Zeitzone Europe/Vienna repräsentieren. Diese Information (Zeitzone) ist im Datentype DATE einfach nicht enthalten!

Zusammenfassung

Der Datentype DATE braucht in der Datenbank weniger Platz und kann schneller verarbeitet werden als Spalten mit dem Datentype TIMESTAMP WITH TIMEZONE. Da ein Umbau des Datentyps sowohl in der Datenbank als auch in der Anwendung umgesetzt werden muss, ist dies recht komplex. Benötigt man nur an wenigen Stellen der Applikation die korrekte Ausgabe der Uhrzeit für den Client, ist es einfacher, dies an dieser Stelle einfach zu berechnen. Die korrekte Berechnung geht mittels folgendem Ausdruck:

cast(from_tz(cast(sysdate as timestamp),'Europe/Vienna') at time zone 'America/New_York' as date)

Wobei im from_tz(cast(sysdate as timestamp),'Europe/Vienna') die Zeitzone genutzt werden muss, in der die Daten vorliegen und in at time zone 'America/New_York' die Zeitzone des Clients angegeben werden muss.

Weiterführende Informationen

Weitere Informationen zum Thema Oracle Datenbank und Zeitzone finden Sie unter anderem in
dem Blog Setting Database Time Zones in Oracle.