Mit Oracletimestamp und PHP rechnen

[Christian Seiler]

Hallo Hauke,

ich bin noch ziemlich neu in Sachen Internetprogrammierung, daher bitte nicht mit Fachwörtern oder so um euch schmeißen

Ich habe versucht, mein Posting möglichst einfach zu halten, aber die Materie ist auf den zweiten Blick sehr komplex, wenn also etwas unverständlich ist, frage bitte nach.

und nicht auslachen, wenn es eine doofe Frage ist, also:

Die Frage ist alles andere als doof. Und die Antwort ist alles andere als trivial.

Ich habe eine Tabelle die AdminRevert heißt. Dort ist ein Feld mit dem Namen "WANN" als Timestamp(6) definiert worden.

Erst einmal grundsätzlich zum Begriff "Timestamp". Es gibt Timestamps, wie sie die POSIX-Spezifikation definiert und es gibt den Datentyp "TIMESTAMP" in Oracle. Und es gibt den Datentyp TIMESTAMP in MySQL. Und mit Sicherheit noch ein paar andere Dinge mehr. Das sind jedoch alles völlig verschiedene Dinge, mal abgesehen von der Tatsache, dass sie mit Datum und Zeit zu tun haben.

Fangen wir mit POSIX-Timestamps an: Ein POSIX-Timestamp sind die Anzahl an Sekunden, die seit 1970-01-01T00:00:00+00:00 (also dem 1. Januar 1970 Mitternacht Universalzeit) vergangen sind, allerdings ohne Schaltsekunden zu berücksichtigen (nachdem Oracle meines Wissens auch keine Schaltsekunden berücksichtigt, werde ich das nicht weiter erwähnen). Um mal ein Beispiel zu geben: Aktuell ist es 2006-11-26T11:52:07+0100 hier (d.h. der 26. November 2006, 11:52:07 Mitteleuropäische Standardzeit) und der zugehörige POSIX-Timestamp zu diesem Datum ist 1164538327, d.h. so viele Sekunden sind seit 1970-01-01T00:00:00+00:00 (der Zeitpunkt wird übrigens auch POSIX- oder UNIX-Epoche genannt) vergangen. Wenn Du wissen willst, wie viele Sekunden zwischen zwei Ereignissen vergangen sind, deren Zeitpunkte Du in Form von POSIX-Timestamps besitzt, dann subtrahierst Du einfach beide Timestamps voneinander.

Timestamps haben allerdings einen großen Nachteil: Auf 32bit-Systemen stehen nur 32 bit für den Datentyp zur Verfügung. Deswegen fangen sie nach 2038 wieder von vorne an (Integer-Overflow). Allerdings: Bis dahin dürften Systeme mit 64 bit schon so verbreitet sein, dass sich das Problem von selbst gelöst haben dürfte.

Nun gibt es in Oracle auch einen Datentyp TIMESTAMP. Der wird auch verwendet, um Datum- und Zeitwerte zu speichern; wie Oracle den auf der Festplatte ablegt, weiß ich nicht, aber Du bekommst ihn über einen SELECT immer nur in formatierter Form heraus. Der TIMESTAMP-Datentyp nimmt folgende Werte auf: Jahr, Monat, Tag, Stunde, Minute, Sekunde und Sekundenbruchteile (die Präzisionsangabe, bei Dir 6 (wg. TIMESTAMP(6)), gibt nur die Präzision der Sekundenbruchteile an, alles andere wird immer gespeichert). Der TIMESTAMP-Datentyp hat allerdings ein großes Problem: Er speichert keine Zeitzoneninformationen, d.h. Dein Beispiel »23-11-06 05:22:03.32342 pm« kann sowohl etwa halb sechs sein oder auch halb zwölf (wenn Du annimmst, dass die Uhrzeit diejenige für Perú, z.B. in Lima, ist, dann wäre es hier zur gleichen Zeit halb zwölf). Gut, Du kannst natürlich argumentieren, dass Du hier nur die Deutsche Zeit speicherst und deswegen keine Problem bekommst, aber das stimmt nicht - in Deutschland gibt's nämlich zwei Zeitzonen: Mitteleuropäische Standardzeit (UTC+01:00) und Mitteleuropäische Sommerzeit (UTC+02:00). Wenn Du also »23-11-06 05:22:03.32342 pm« Lokalzeit (Für das Datum wäre das Standard- oder Winterzeit) in UTC umwandelst, dann erhälst Du »23-11-06 04:22:03.32342 pm«. Wenn Du allerdings »23-08-06 05:22:03.32342 pm« Lokalzeit (Für das Datum wäre das Sommerzeit) in UTC umwandlest, dann erhälst Du »23-08-06 03:22:03.32342 pm«.

Zusammengefasst: Der Datentyp TIMESTAMP in Oracle ist höchst problematisch, was das Speichern von festen Zeiten angeht - denn er setzt immer eine von außen kommende Kenntnis der Zeitzone voraus, die nicht mit dem Zeitpunkt zusammen gespeichert wird. Ferner stellt sich das Problem, dass selbst wenn die Zeitzone bekannt ist, die Umrechnung in UTC ein absoluter Krampf sein kann, weil man zusätzlich noch die Regeln zur Umstellung zur Sommerzeit etc. kennen muss. Gut, man kann sich fragen, wozu man das überhaupt in UTC umwandeln will, dazu weiter unten.

Nach dieser allgemeinen Einführung erst einmal zu Deinen Fragen:

1. Frage (eigentlich nur so nebenbei), gibt es eine Möglichkeit, den Timestamp auf die aktuelle Uhrzeit zu bringen, wenn man ein Insert, oder Update macht? Also ich meine das Feld.

Du hast zwei Möglichkeiten:

1. Du setzt beim INSERT und UPDATE immer das Feld auf SYSTIMESTAMP:

INSRET INTO tabelle (..., wann, ...) VALUES (..., SYSTIMESTAMP, ...);
UPDATE tabelle SET ..., wann = SYSTIMESTAMP, ... WHERE ...

2. Du schreibst Dir einen Trigger, der so etwas automatisch macht, in etwa so (ungetestet, ich habe schon lange nichts mehr mit Oracle gemacht, kann sein, dass da noch ein Fehler drin ist):

CREATE TRIGGER tabelle_datum_aktualisieren
BEFORE INSERT OR UPDATE ON tabelle
REFERENCING NEW AS n
FOR EACH ROW
BEGIN
  SELECT SYSTIMESTAMP FROM dual INTO :n.wann;
END;

2. Frage ist etwas umständlicher. Ich möchte mit PHP diesen Timestamp auslesen. Komischerweise wird der Timestamp in der Datenbank mit zB 23-11-06 05:22:03.32342 pm gespeichert (oder so ähnlich) und wenn ich den Wert auslese und stump ausgebe, wird er im 24 Stunden-Format ausgegeben. Macht PHP das von alleine?

Nein, das macht Oracle. Es wandelt einen TIMESTAMP automatisch in einen String um, wenn er im Result-Set auftaucht. Die Formatierung ist allerdings irgend eine Systemeinstellung in der Datenbank. In Oracle gibt es eine Funktion TO_CHAR(), mit der Du die Formatierung beeinflussen kannst, TO_CHAR(wann, 'YYYY') würde Dir beispielsweise das Jahr zurückliefern, unter Format Models ist beschrieben, was es alles für Formatierungsmöglichkeiten gibt.

Nun wollte ich gerne aber berechnen lassen, wieviel Zeit zwischen dem Timestamp und jetzt vergangen ist.

Das wird jetzt allerdings Trickreich.

Ich hatte das mit Oracle schon geschafft :

select systimestamp - (select wann from adminrevert where ident=1) from dual;

Du denkst, dass Du es geschafft hast. Das ist aber falsch. Denn Oracle speichert ja beim Timestamp-Datentyp keine Zeitzoneninformationen und wenn Du nun die Differenz zwischen einem Datum in Sommerzeit und jetzt (Winterzeit) bildest, dann wird Oracle eine Stunde daneben liegen, da Oracle das Datum nur direkt vergleichen kann, aber keine Ahnung hat, welchen Offset das ursprüngliche Datum hatte.

Anders ausgedrückt: Mal angenommen, JETZT wäre 2006-10-29T04:00:00+01:00, also direkt nach der Umstellung zur Winterzeit. Mal angenommen, Du hättest am gleichen Tag um 01:00 morgens zuletzt den Eintrag in der Tabelle geändert (war noch Sommerzeit). Oracle wird Dir als Differenz 3 Stunden ausspucken, tatsächlich waren es 4 Stunden. Gut, das Beispiel ist jetzt konstruiert, aber im Endeffekt werden Dir keine korrekten Ergebnisse geliefert, wenn sich die beiden Daten nicht im gleichen UTC-Offset befinden.

Ferner: Dein SELECT ist ziemlich umständlich, Du bastelst da einen Subselect rein, der gar nicht nötig ist. Du kannst schlichtweg machen:

SELECT SYSTIMESTAMP - wann AS differenz FROM adminrevert WHERE ident = 1;

Das hat den Vorteil, dass Du damit auch mehrere Datensätze auf einmal holen kannst, z.B.

SELECT weitere_felder, SYSTIMESTAMP - wann AS differenz FROM adminrevert (das würde alle Datensätze der Tabelle holen)

aber wenn ich das in PHP in eine query setze, weiß ich nicht, wie ich den wert auslesen kann.

Wenn Du zwei TIMESTAMPs voneinander subtrahierst, dann ist das Ergebnis in Oracle etwas vom Datentyp INTERVAL. Wenn Du damit irgend etwas sinnvolles anfangen willst, wäre es eventuell hilfreich, sich die Funktion EXTRACT anzusehen:

SELECT EXTRACT(DAY FROM (SYSTIMESTAMP - wann)) AS differenz_tage FROM adminrevert WHERE ident = 1;

Damit erhälst Du die Anzahl an Tagen, die zwischen beiden Ereignissen liegen, ähnlich kommst Du an andere Werte, schau Dir einfach mal die Funktion EXTRACT()  an.

Allerdings musst Du dann mit obigem Fehler leben.

Daher wollte ich wissen, ob man mit dem Date() Befehl von PHP da irgendwas machen kann. Oder eine andere Formatierung von Timestamp benutzen kann.

Du kannst mittels TO_CHAR() ja Timestamps durchaus anders Formatieren, Du kannst z.B.

SELECT ..., TO_CHAR(wann, 'YYYY-MM-DD HH24:MI:SS') AS wann, ... FROM adminrevert ...

machen. Das Ergebnis dieser Abfrage kannst Du dann wiederum in PHP leicht in einen POSIX-Timestamp verwandeln, dazu kennt PHP die Funktion strtotime(). D.h. wenn in $wann das Ergebnis der Spalte der Abfrage stünde, könntest Du über

$wann = strtotime ($wann);

aus diesem Datum einen POSIX-Timestamp machen. Das Format, das ich bei TO_CHAR angegeben habe, ist eins, das PHP immer versteht, bei anderen Formaten kann es zu Komplikationen kommen und viel wichtiger: das Standardformat von Oracle ist nicht festgelegt, sondern kann man irgendwo einstellen; TO_CHAR() stellt dagegen sicher, dass das Format immer stimmt. PHP verwendet zum Umwandeln immer die lokale Zeitzone auf dem Server und berücksichtigt auch Sommer-/Winterzeit, d.h. Daten werden korrekt umgewandelt (unter der Voraussetzung, dass die Zeitzone in der Datenbank identisch mit der Zeitzone auf dem System, auf dem PHP läuft, ist und Du im Falle PHP >= 5.1 nicht mit set_default_time_zone o.ä. rumgespielt hast).

Den aktuellen POSIX-Timestamp liefert PHP Dir über time(). Du kannst dann den $wann-Timestamp vom aktuellen POSIX-Timestamp abziehen und erhälst die Differenz zwischen beiden Daten in Sekunden; wenn Du die Anzahl an Tage wissen willst, kannst Du das ganze dann bspw. durch 86400 teilen.

Generell ist zu sagen, dass man TIMESTAMP in Oracle eigentlich meiden sollte und grundsätzlich nur TIMESTAMP WITH TIME ZONE oder TIMESTAMP WITH LOCAL TIME ZONE einsetzen sollte, denn dort ist es kein Problem, an Zeitzoneninformationen zu kommen und so korrekte Berechnungen durchzuführen bzw. auch in Oracle durchführen zu lassen. Ich hatte irgendwann sogar einmal ein paar PL/SQL-Funktionen geschrieben, die TIMESTAMP WITH TIME ZONE in einen POSIX-Timestamp umwandeln können und umgekehrt - leider finde ich die nicht mehr.

Es gibt allerdings auch sinnvolle Einsatzzwecke von TIMESTAMP - z.B. bei der Angabe von Arbeitszeiten: Es ist ja schließlich egal, ob nun Sommer- oder Winterzeit: Der Arbeitszeitanfang verschiebt sich in der Regel nicht (anders gesagt: man fängt im Sommer in der Regel nicht eine Stunde früher mit der Arbeit/Schule/Uni an). Deswegen hat TIMESTAMP durchaus seine Berechtigung in Oracle. Für Protokollierungszwecke ist er jedoch völlig ungeeignet, wie ich oben dargelegt habe. Falls es Dir nicht möglich ist, das Datenbankdesign zu ändern, so würde ich die PHP-Methode empfehlen, um das Zeitzonenproblem so zu umgehen.

Schließlich sei noch zu erwähnen, dass es noch einen Uralt-Datentyp in Oracle gibt, der DATE heißt. Der kann auch Datums- und Zeitwerte (ohne Zeitzone versteht sich) aufnehmen, hat allerdings im Gegensatz zu TIMESTAMP (wurde in Oracle 9i eingeführt, wenn ich mich richtig erinnere) einige sehr unschöne Semantiken, gerade was das Rechnen mit Daten angeht - TIMESTAMP und TIMESTAMP WITH ... sind dagegen sehr sauber konzipiert, während DATE nur ein Überbleibsel ist, das man wirklich meiden sollte.

Viele Grüße, Christian