Heute möchte ich einige Fragen und Antworten zum Thema "Umgang mit Datumswerten" zusammenfassen. Man hat ja immer wieder damit zu tun.
Zunächst stellt sich ja recht häufig die Frage, ob man DATE oder TIMESTAMP verwenden sollte. TIMESTAMP ist ja der "jüngere" und mächtigere Datentyp. Andererseits kommt es immer noch häufig vor, dass Werkzeuge nicht richtig mit TIMESTAMP umgehen können. Aus diesem Grund wird DATE immer noch recht häufig (selbst auf 10g oder 11g-Datenbanken) genutzt. Das ist jedoch erstmal kein Problem - solange man keine speziellen "Features" des TIMESTAMP benötigt, sind beide Typen gleichwertig. Zunächst sollte man also diese Featues des TIMESTAMP herausarbeiten.
- Die kleinste Einheit eines DATE ist die Sekunde, ein TIMESTAMP kommt auf die Millisekunde herunter.
- TIMESTAMP kann mit Zeitzonen umgehen - es gibt den Datentypen TIMESTAMP WITH TIME ZONE und SQL-Funktionen wie FROM_TZ, die recht hilfreich beim Konvertieren zwischen Zeitzonen sind.
Weder DATE noch TIMESTAMP speichern das Datum intern als Zahl (wie Unix es macht). Das kann man mit der DUMP-Funktion auch einfach selbst nachsehen (Man sieht aber bereits an den Bytes, dass TIMESTAMP intern mit UTC arbeitet):
select sysdate, dump(sysdate) bytes from dual / SYSDATE BYTES ------------------- ---------------------------------------- 21.09.2009 12:13:50 Typ=13 Len=8: 217,7,9,21,12,13,50,0 select systimestamp, dump(systimestamp) bytes from dual / SYSTIMESTAMP BYTES ----------------------------------- ------------------------------------------------------------------------------ 21.09.2009 12:15:21,413578 +02:00 Typ=188 Len=20: 217,7,9,21,10,15,21,0,16,179,166,24,2,0,5,0,0,0,0,0
"217,7" wird hexadezimal als "D9","07" ausgedrückt. Als 16bit-Zahl wäre das "07D9" und das wiederum ist in dezimaler Schreibweise 2009. Man sieht also, dass Jahre, Monate, Tage, Stunden, Minuten und Sekunden explizit im DATE bzw. TIMESTAMP-Wert drinstecken.
DATE und TIMESTAMP erlauben Datumsarithmetik. Und beide erlauben das Arbeiten mit numerischen Werten und mit Intervall-Datentypen. Die meisten von euch arbeiten sicherlich mit numerischen Werten: Dabei ist die "Einheit", mit der man arbeitet, 1 Tag. Ein "DATE" ist also "ein Tag" (für TIMESTAMP gilt das gleiche). Eine Stunde ist demnach 1/24 DATE, eine Minute ist 1/1440 DATE und so weiter. Möchte man also wissen, welche Zeitspanne zwischen zwei DATE-Werten liegt, so zieht man sie zunächst mal voneinander ab ...
select
to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
to_date('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS') DATETIME_INTERVAL
from dual
/
DATETIME_INTERVAL
-----------------
62,302083333
Bei DATE-Werten wird das Ergebnis in einen numerischen Wert konvertiert. Die Einheit ist "DATE", also Tage. Es kommen also 62 und ein knapper "Drittel-Tag" heraus. Verwendet man TIMESTAMP, so bekommt man sofort einen Intervall-Datentypen (hier: Day-To-Second) ...
select
to_timestamp('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
to_timestamp('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS') DATETIME_INTERVAL
from dual
/
DATETIME_INTERVAL
------------------------------------------------
+000000062 07:15:00.000000000
Den numerischen Wert kann man allerdings auch in einen Intervall-Datentypen konvertieren ...
select numtodsinterval( to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') - to_date('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS') ,'DAY' ) DATETIME_INTERVAL from dual / DATETIME_INTERVAL ----------------------------------------------- +000000062 07:14:59.999999999
... allerdings zeigt sich hier ein kleines Rundungsproblem. Besser wäre es also, sich das Ergebnis der Datumsarithmetik auch bei Verwendung von DATE direkt als Intervallwert zurückgeben zu lassen. So vermeidet man den Umweg über NUMBER:
select ( to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') - to_date('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS') ) day(9) to second DATETIME_INTERVAL from dual / DATETIME_INTERVAL ------------------------------------------ +000000062 07:15:00.000000000
Man kommt also sowohl mit DATE als auch mit TIMESTAMP zu einem in SQL*Plus gut lesbaren Ergebnis. Aber man arbeitet ja nicht immer in SQL*Plus und manchmal benötigt man eben strukturierten Zugriff auf die einzelnen Elemente: Mit der EXTRACT-Funktion kommt man an die einzelnen Elemente des Intervalls heran:
select extract( HOUR from ( to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') - to_date('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS') ) day(9) to second ) INTERVAL_HOURS from dual / INTERVAL_HOURS -------------- 7
Minuten gehen dann analog ...
select extract( MINUTE from ( to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') - to_date('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS') ) day(9) to second ) INTERVAL_MINUTES from dual / INTERVAL_MINUTES ---------------- 15
Geht es ums Addieren oder Subtrahieren von einem vorhandenen Zeitstempel, so bieten sich auch wieder die numerischen Datentypen als auch Intervall-Datentypen an. Wir möchten 12 Stunden zu einem Datum addieren ... Erstmal numerisch ...
select
to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') + (12/24) NEW_TIME
from dual
/
NEW_TIME
-------------------
22.09.2009 02:45:00
select
to_timestamp('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') + (12/24) NEW_TIME
from dual
/
NEW_TIME
-------------------
22.09.2009 02:45:00
... welche sowohl mit DATE als auch mit TIMESTAMP funktioniert. Aber Achtung: In letzterem Fall hat die Datenbank implizit in ein DATE konvertiert. Und wenn man mit der DUMP-Funktion nachsieht, stellt man fest, dass man auch einen DATE zurückbekommt ... Außerdem kann man sich nun schon denken, dass es hier Rundungsdifferenzen geben kann, besonders wenn man anfängt, einzelne Minuten oder Sekunden zu addieren oder abzuziehen. Besser sind also die Intervall-Datentypen:
select
to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') + interval '12' hour NEW_TIME
from dual
/
NEW_TIME
-----------------------------------
22.09.09 02:45:00,000000000
Das geht übrigens auch in PL/SQL - allerdings kann man, möchte man die Anzahl Stunden dynamisch gestalten, dieses SELECT nicht genauso in einem PL/SQL-Block verwenden. Man muss hier ein wenig anders arbeiten:
declare v_zeit timestamp; v_hrs interval day(9) to second; begin -- 0 days, 12 hours, 0 minutes, 0 seconds v_hrs := '0 12:00:00'; v_zeit := to_timestamp('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') + v_hrs; dbms_output.put_line(v_zeit); end; /
Intervalle können auch einfach aus Zahlenwerten heraus berechnet werden. Möchte man bspw. wissen, was 2,65 Tage genau sind, dann lässt sich das wie folgt ermitteln:
select numtodsinterval(2.65, 'DAY') interval from dual / INTERVAL ---------------------------------------------------- +000000002 15:36:00.000000000
Die Funktion lässt sich noch anders nutzen: Angenommen, es sind keine 2,65 Tage, sondern 2,65 Stunden gemeint. Auch das lässt sich interpretieren ...
select numtodsinterval(2.65, 'HOUR') interval from dual / INTERVAL ---------------------------------------------------- +000000000 02:39:00.000000000
Passend dazu gibt es neben dem Day-To-Second-Intervall auch das Year-To-Month-Intervall. Angenommen, wie haben 4,55 Jahre: Was bedeutet das eigentlich ...?
select numtoyminterval(4.55, 'YEAR') intervall from dual / INTERVALL ---------------------------------------------------- +000000004-06
Also 4 Jahre und 6 Monate. Nun wenden wir das auf ein Beispiel mit zwei Datumswerten an:
select
(
to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
to_date('2007-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS')
) year(9) to month DATETIME_INTERVAL
from dual
/
DATETIME_INTERVAL
-------------------
+000000002-02
Nun wäre es ja schön, wenn man das kombinieren könnte. Wir möchten also wissen, wieviele Jahre, Monate, Tage, Stunden, Minuten und Sekunden zwischen zwei Datumswerten vergangen sind. Wie haben den Intervalltypen Year-To-Month für Jahre und Monate und Day-To-Second für Tage bis Sekunden. Dies gilt es nun zu kombinieren: Dazu ermittelt man zunächst das Intervall Year-To-Month, zieht dieses vom späteren "Enddatum" ab und ermittelt dann vom Rest das Intervall Day-To-Second.
declare
v_enddate date := to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS');
v_startdate date := to_date('2007-07-15 07:30', 'YYYY-MM-DD HH24:MI:SS');
v_ym interval year(9) to month;
v_ds interval day(9) to second;
begin
-- Jahre und Monate ermitteln
v_ym := (v_enddate - v_startdate) year to month;
-- Jahr und Monate vom Enddatum abziehen
v_enddate := v_enddate - v_ym;
-- Tage, Stunden, Minuten und Sekunden ermitteln
v_ds := (v_enddate - v_startdate) day(9) to second;
dbms_output.put_line('Years: '||extract(YEAR from v_ym));
dbms_output.put_line('Months: '||extract(MONTH from v_ym));
dbms_output.put_line('Days: '||extract(DAY from v_ds));
dbms_output.put_line('Hours: '||extract(HOUR from v_ds));
dbms_output.put_line('Minutes: '||extract(MINUTE from v_ds));
dbms_output.put_line('Seconds: '||extract(SECOND from v_ds));
end;
/
Years: 2
Months: 2
Days: 6
Hours: 7
Minutes: 15
Seconds: 0
Ich denke, das wäre es dann für heute; wie man mit TIMESTAMP und Zeitzonen umgehen kann, verschiebe ich aufs nächste Blog-Posting.
