Skip to content

Zeitstempel in Datenbanken (Oracle)

Posted by Bernd Eckenfels on
Zu meinem Job als Software Architekt gehört es an Guidelines für die Implementierung von Software Systemen mitzuarbeiten. Ganz konkret habe ich mich etwas in das Thema Datum/Zeit Speicherung in Datenbanksystemen gestürzt. Aus dem Grund kann ich hier etwas über die Datum/Zeit Repräsentation in Oracle DB 10g Release 1 (und natürlich älteren Modellen) berichten. Gerade im Zusammenspiel mit Zugriffen via JDBC sind hier einige Dinge zu beachten. Beim Design unserer Software achten wir auf die TCO. Das klingt zwar wie ein Buzz-Word, macht aber durchaus Sinn: das System soll möglichst wenig Anforderungen an die Konfiguration der Datenbank stellen (damit diese für unterschiedliche Anwendungen genutzt werden kann). Ebenso soll es möglichst unempfindlich gegen Konfigurationsänderungen und Variationen sein (gerade im Bereich Zeit/Datum können Probleme mit unterschiedlichen Datums-Formaten oder Sprachen auftreten.)

Oracle Datum/Zeit Datentypen

Prinzipiell unterstützt Oracle den SQL Datentyp "DATE", erweitert um Zeitinformationen, bis hin zur Sekunde. Der Typ hat keine Möglichkeit der Speicherung von Schaltsekunden. Er belegt 7 bytes, wobei Jahrhundert, Jahr, Monat, Tag, Stunde, Minute und Sekunde in getrennten Feldern gespeichert werden. Es werden keine zusätzlichen Zeitzonen-Informationen gespeichert und der Wertebereich reicht von v.Chr 4712-12-31 00:00 bis 9999-12-31 23:59:59.

Das Speicherformat eines Datenbank Feldes oder eines temporären Typen kann man mit Oracle leicht mit der dump() Funktion inspizieren: SQL> INSERT INTO schema.tabelle (datumspalte) VALUES (TO_DATE('18-FEB-2005 23:12:19', 'dd-Mon-yyyy HH24:mi:SS'); SQL> SELECT datumspalte,dump(datumsspalte) FROM schema.tabelle; 2005-02-18 23:12:19 Typ=12 Len=7: 120,105,02,18,24,13,20 Jahrhundert und Jahr werden mit einem 100 Offset, Stunde, Minute und Sekunde mit einem Offset von 1 gespeichert, um 0-bytes zu vermeiden. (Und um Jahreszahlen vor Christus darzustellen. Das Jahr 0 gibt es bei Oracle nicht). Zwischenergebnisse und Systemfunktionen kennen des weiteren noch einen internen DATE Typ, der aber Plattform-spezifisch ist: SQL> SELECT dump(SYSDATE) FROM dual; Typ=13 Len=8: 213,7,2,18,23,12,19,0 // 7*256+213=2005 Hier wird die Zeit 2005-02-18 23:12:19 auf x86 dargestellt. Es ist natürlich unbefriedigend, dass Oracle hier keine genauere Zeitauflösung bietet, und auch die Unterstützung von UTC Zeiten bzw. Zeitzonen fehlen. Deswegen bietet Oracle zwei weitere Datentypen für die Darstellung von Zeitstempeln an. Diese bieten eine Auflösung von Sekunden mit 0-9 Nachkommastellen. Es handelt sich dabei im den Typ TIMESTAMP oder TIMESTAMP WITH TIME ZONE. Wie auch schon bei DATE gibt es hier eine interne Repräsentation, damit umfasst die Liste der Datum/Zeit Typen (ohne Intervalle):
TypTyp NameBytes
12DATE7
180TIMESTAMP(0)7
180TIMESTAMP(1-9)11
181TIMESTAMP(0) WITH ZIME ZONE13
181TIMESTAMP(1-9) WITH ZIME ZONE13
231TIMESTAMP(0) WITH LOCAL TIME ZONE7
231TIMESTAMP(1-9) WITH LOCAL TIME ZONE11
13 intern DATE8
188intern TIMESTAMP20
Die Anzahl der Sekunden Nachkommastellen hat, wie man in der Tabelle sehen kann keine Auswirkung auf das Speicherformat (außer bei Typen ohne Sekundenbruchteile). Wenn keine Precision bei TIMESTAMP angegeben wurde sind dies 6 Stellen, das entspricht der Darstellung als Java Millisekunden. Mit dem Maximum von 9 Stellen können Nanosekunden wie in java.sql.TIMESTAMP#getNanos() abgedeckt werden.

Literale

Wenn in einem Ausdruck ein Zeichenstring als DATUM oder TIMESTAMP interpretiert werden soll, so führt Oracle eine Implizite Konvertierung durch. Diese implizite Konvertierung ist aber abhängig von den NLS Einstellungen, und sollte deswegen nie verwendet werden. Besser ist es Zeitstempel explizit mit Funktionen zu konvertieren, oder als Literal anzugeben. Beim DATE Typ wird allerdings nur das ANSI SQL Literal unterstützt: DATE '2005-02-18' also keine Zeitwerte. Hier ist es unvermeidlich die Konvertierungsfunktion zu verwenden: TO_DATE('2005-02-18 23:12:19', 'YYYY-MM-DD HH24:MI:SS') Das TIMESTAMP Literal ist hier deutlich praktischer: TIMESTAMP '2005-02-18 23:12:19.123456'. Und gibt es auch in einer Version mit Zeitzonen oder Zeitoffsets: TIMESTAMP '2005-02-18 23:12:19.123 +01:00' TIMESTAMP '2005-02-18 23:12:19.123 Europe/Berlin CET' (Die Angabe von Zeitzonen Namen erfordert zusätzlich die Angabe des kurzen Zeitzonen Namens zur Unterscheidung von Sommer/Winterzeit. Es ist deswegen in den meisten Situationen deutlich besser nur mit Zeit-Offsets zu arbeiten)

Zeitzonen

Informationen zur Zeitzone können als Offset (In Stunden und Minuten) von der UTC Zeit dargestellt werden. Jedoch sind zusätzlich auch Informationen zur Start/Ende Zeit der Sommerzeit notwendig. Bei einer verteilten Anwendung können hier verschiedene Zeitzonen zusammenspielen: Zeitzone des DB-Server Betriebsystems, Zeitzone des DB-Client Betriebsystems. Zeitzone der Datenbank, Zeitzone der Client Sitzung (Session). Bei einer 3tier Anwendung kommt dann noch die Zeitzone dazu die Benutzer am Web/Client Front-end sehen möchten. Die Zeitzone der Datenbank wird bei der Einrichtung aus der Zeitzone des DB-Server Betriebsystems initialisiert, kann aber mit "ALTER DATABASE SET time_zone = 'UTC';" gesetzt werden (Restart erforderlich). Diese Zeitzone wird nur als Basis für den Typ "TIMESTAMP WITH LOCAL TIME ZONE" benutzt. Oracle empfiehlt die Verwendung von UTC Zeiten um Datensynchronisation und Backup/Restore zwischen verschiedenen Standorten zu erleichtern. Die Zeitzone der Client Session wird aus der Zeitzone des Client Betriebsystems ermittelt, lässt sich über eine Environment Variable vorgeben, oder mittels "ALTER SESSION SET time_zone = 'Europe/Berlin';" setzen. Die Session Zeitzone wird benutzt bei der Speicherung von Daten in die Felder "TIMESTAMP WITH [local] TIME ZONE", es sei denn eine Zeitzone wurde explizit angegeben. Die Betriebsystem Zeitzone des DB-Servers wird immer für die Ausgabe der Systemfunktionen SYSDATE und SYSTIMESTAMP verwendet. Da die Zeitstempel in einem "TIMESTAMP WITH LOCAL TIME ZONE" Feld immer auf die Zeitzone der Datenbank normiert werden, erlaubt Oracle die Veränderung der Datenbank Zeitzone nicht, wenn diese Daten vorliegen. Ebenfalls sind die Daten bei einem Backup dann immer relativ zu dieser Zeitzone. Im Sinne der Reduktion von Abhängigkeiten ist es meiner Meinung nach geboten, auf diesen Datentyp gänzlich zu verzichten.

Oracle und JDBC

So weit sind die Fakten klar, und man kann z.B. entscheiden alle Zeiten immer als UTC in einen TIMESTAMP Typen zu speichern, dabei die Zeitzone der DB und des Servers ignorieren, keine NLS Formatstrings implizit zu verwenden. Die Auswahl von UTC als Speicherzeitzone hat einige Vorteile:
  • es sind keine zusätzlichen bytes zur Speicherung der Zeitzone notwendig
  • die DB Zeitzone kann jederzeit geändert werden, oder das Backup kann jederzeit in eine DB mit anderer Zeitzone eingespielt werden, da diese einfach nicht beachtet wird
  • Interpretationen bezüglich der gewünschten Zeitzone/DST fallen nicht an, somit sind DB und Session Zeitzone unerheblich
Die Frage ist nun, wie diese Vorgaben im JDBC Treiber (und evtl eingesetzten Persistence Layer oder der CMP der unterschiedlichen J2EE Application Server) umgesetzt werden können. Immerhin soll das System ja möglichst robust gegen Konfigurationsänderungen sein, und keine allzu engen Grenzen für die Parametrisierung der Oracle Datenbank stellen. Und genau darauf werde ich im nächsten Artikel eingehen.

Zum weiterlesen

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

Thomas on :

Hallo, wirklich sehr interessantt und umfassend. Ich bin auf die Seite gestossen, weil ich genau zu dem Thema eine Antwort suche, die im nächsten Artikel besprochen werden soll, habe. Konkret: WAS 6, CMP und wie bleibe ich bei UTC?! Wann soll er denn kommen? MFG Thomas

eckes on :

Hallo Thomas, ja leider bin ich noch nicht dazu gekommen. Ich plane zuerst das ganze von der JDBC und dann von der Jboss-CMP Seite anzusehen, weiss noch nicht ob man das Verhalten der anderen EJB Container davon ableiten kann. Hast du schon Versuche gemacht? Betreibt Ihr eine Oracle DB mit Datenfeldern in UTC mit/ohne Umstellung der DB Timezone? Gruss Bernd

Thomas on :

Hi Bernd, Wir haben ein Datenmodell entwickelt mit genau dem Gedanken, den du auch im Artikel anführst: Komplett auf Zeitzoneninformationen verzichten und alles als UTC bzw. GMT Zeit Speichern. Ich benutze also in 10g den Datentyp Timestamp. So weit so gut. In WAS habe ich nun ein CMP für so eine Tabelle und möchte einen Create() machen. Ich übergebe also einen java.sql.Timestamp, den ich mit System.currentMillis() initialisiere. Das Problem: Der Zeitwert ist immer in CET Zeitzone, dabei gibt System.currentMillis() lt. Sun immer einen Zeitzonenunabhängigen Wert zurück. Ich weiss einfach nicht, warum die Zeit nicht in UTC gespeichert wird. Hast du eine Idee? Danke, Thomas

eckes on :

Ich glaube es könnte was helfen ein Calender Object statt einem Timestamp zu nehmen und dort die Zeitzone explizit zu setzen. Ansonsten hilft wohl nur die Zeitzone der Server-VM auf UTC zu stellen. Aber wie gesagt, das muss ich mir noch ansehen. Gruss Bernd

Thomas on :

Hallo, das mit dem Calendar funktioniert nicht, da meine CMP methode auf ein java.sql.Timestamp besteht. Das mit der Zeitzonenumstellung sollte aber funktionieren. Habe nun mal OS Zeitzone umgestellt und dann wird der Wert auch als UTC geschriben. Wäre echt interessant, woher her diese Info nun bezieht - aus der JDBC session mit dem Application Server oder einfach vom Betriebssystem... Fragen über Fragen. Danke, Thomas

eckes on :

Meine Vermutung ist dass TimeStamp#toString() verwendet wird vom Treiber um ein ANSI SQL String Literal zu erzeugen. Diese Methode ruft super.getHours() auf, was Date.getHours() ist und das wiederum benutzt den staticCal, des es wenn keiner bekannt ist mittels staticCal.setTimeZone(TimeZone.getDefault()); staticCal.setTimeInMillis(fastTime); erzeugt. D.h. die Default Timezone neu setzen hilft, die bekommt er per Default vom OS kann man aber auch überschreiben. Gruss Bernd

Thomas on :

(Comment removed)

eckes on :

Das war ein auszug aus der java.lang.Date Klasse. Dort wird immer ein Kalender verwendet dessen Timezone aus der "TimeZone.getDefault()" gesetzt wird. D.h. einfach eine neue Default Timezone setzen sollte die einzige Möglichkeit sein (eventuell hilft es auch eine eigene TimeStam Klasse zu verwenden deren toString() überschrieben ist. Ich hab aber noch nicht in die JDBC Treiber reindebugged ob die die Methode wirklich verwenden. Beim Start müsste man die TimeZone mit "-Duser.timezone=UTC" setzen können. Oder mit TimeZone.setDefault(TimeZone.getTimeZone("UTC")) wenn das der Security Manager im AppServer mitmacht. Gruss Bernd

Thomas on :

Hi, ahh, jetzt weiss ich, was du meinst! Das habe ich shon probiert und funktioniert inm WAS zumindest nicht. Entweder intern wird nicht mit toString() gearbeitet, oder das ganze läuft in einer anderen VM ab und mein TimeZone.setDefault(TimeZone.getTimeZone("UTC")) läuft ins Leere. Evtl. reicht ja schon ein anderer Thread - ich kenne den Scope der DefaultTimezone nicht. Gruss, Thomas

eckes on :

Der Scope dürfte, da es ein static ist der Classloader sein. Da es aber ne Systemklasse ist isses wohl AppServer weit. Wenn das setzen nichts bringt, dann eventuell über die Commandline. Naja, ich melde mich wenn ich mir das näher angeschaut habe. Die globale Zeitzone des Appservers umstellen dürfte auch nicht so problemlos sein. BTW: auch im WAS müsste man im CmP Layer eigene Konverter registrieren können, JBoss kann das für (Benutzer)Datentypen wohl. Gruss Bernd

Rudolf Galfi on :

(Comment removed)

Kai-Uwe Schaefer on :

Im Oracle9: to_date('01/01/1970', 'MM/DD/YYYY') + numtodsinterval(zeitzahl/1000, 'SECOND')

Fr34k on :

Wie kann ich TIMESTAMP (JETZT) -5min (300sec) angeben?

Bernd Eckenfels on :

Sollte mit einem Intervall gehen.
CODE:
select    systimestamp as now,   systimestamp - to_dsinterval('0 0:05:00') as ago from dual; NOW ------------- 23.04.09 00:28:55,977000000 +02:00 AGO ------------- 23.04.09 00:23:55,977000000 +02:00
Gruss Bernd

Add Comment

BBCode format allowed
Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA