Dates und Timestamps in der Oracle-Datenbank

Die Arbeit mit Datums-, Zeit- und Intervalltypen ist in jeder Programmiersprache und Datenbank immer wieder eine gewisse Herausforderung. Auch Oracle stellt hier keine Ausnahme dar. Ich habe im Folgenden ein paar Fakten zu Datums-, Zeit- und Intervalltypen zusammengefasst, die mir in den vergangenen Jahren bei der Entwicklung auf Oracle-Datenbanken besonders wichtig erschienen und die hoffentlich einen schnellen Einstieg in dieses Thema ermöglichen.

Oracle kennt vier Datentypen, um Datums- und Zeitinformationen zu speichern:

Datentyp	Beschreibung
DATE	Datum inkl. Uhrzeit. Wird sekundengenau gehalten. Beispiel: 20.12.2014 17:45:12
TIMESTAMP	Timestamps werden mit einer Genauigkeit von Sekundenbruchteilen gehalten. Es sind die Typen TIMESTAMP(0) (entspricht Date) bis TIMESTAMP(9) (milliardstel Sekunde) möglich. Der Default ist TIMESTAMP(6). Beispiel: 20.12.2013 17:45:12.175161
TIMESTAMP WITH TIME ZONE	Genau wie TIMESTAMP, nur dass zusätzlich eine Information über die Zeitzone gehalten wird. Beispiel: 22.04.12 22:01:41,774000000 +02:00
TIMESTAMP WITH LOCAL TIME ZONE	Genau wie TIMESTAMP WITH TIME ZONE, nur dass bei der Ausgabe des Wertes in die Zeitzone der Session umgerechnet wird, falls diese abweicht.

Dates und Timestamps haben einen maximalen Wertbereich vom 1. Januar 4712 B.C. bis zum 31. Dezember 9999.

Das aktuelle Datum (inkl. Uhrzeit) wird in Oracle per SQL klassischerweise wie folgt ermittelt:


select sysdate from dual;

select sysdate from dual;

Es gibt neben SYSDATE weitere SQL-Funktionen, die den aktuellen Zeitpunkt ausgeben, und die sich alle etwas unterscheiden:

Funktion	Rückgabetyp	Zeitzone
SYSDATE	DATE	des Servers
CURRENT_DATE	DATE	der Session
LOCALTIMESTAMP	TIMESTAMP	der Session
CURRENT_TIMESTAMP	TIMESTAMP WITH TIMEZONE	der Session
SYSTIMESTAMP	TIMESTAMP WITH TIMEZONE	des Servers

Mit der Funktion CAST lassen sich Zeitdatentypen ineinander konvertieren. Hier ein paar Beispiele:


select cast(systimestamp as timestamp) from dual; 
select cast(sysdate as timestamp) from dual; 
select cast(localtimestamp as date) from dual;

select cast(systimestamp as timestamp) from dual;

select cast(sysdate as timestamp) from dual;

select cast(localtimestamp as date) from dual;

In welchem Format Datums- und Zeitangaben standardmäßig ausgegeben werden, hängt von der Sessionvariable NLS_DATE_FORMAT ab, die sich wie folgt ermitteln und ändern lässt:


select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Möchte man in der Ausgabe ein hiervon abweichendes Format haben, kann ein Timestamp oder Date mit der Funktion TO_CHAR in einen formatierten String gewandelt werden:


select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') from dual;
select to_char(localtimestamp,'dd.mm.yyyy hh24:mi:ss:ff4') from dual;
select to_char(systimestamp,'dd.mm.yyyy hh24:mi:ss:ff tzh:tzm') from dual;  --inkl. Zeitzone

select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') from dual;

select to_char(localtimestamp,'dd.mm.yyyy hh24:mi:ss:ff4') from dual;

select to_char(systimestamp,'dd.mm.yyyy hh24:mi:ss:ff tzh:tzm') from dual; --inkl. Zeitzone

Umgekehrt lässt sich auch ein Characterstring mit TO_DATE, TO_TIMESTAMP oder TO_TIMESTAMP_TZ in ein Date oder Timestamp bzw. Timestamp With Time Zone konvertieren.


select to_date('20141201','yyyymmdd') from dual;
select to_timestamp('22-12-2015','dd-mm-yyyy') from dual;
select to_timestamp_tz('22-12-2015 -3:00','dd-mm-yyyy TZH:TZM') from dual;

select to_date('20141201','yyyymmdd') from dual;

select to_timestamp('22-12-2015','dd-mm-yyyy') from dual;

select to_timestamp_tz('22-12-2015 -3:00','dd-mm-yyyy TZH:TZM') from dual;

Bei dem Umgang mit Formatmodellen gibt es sehr viele Möglichkeiten. Siehe hierzu die Oracle-Dokumentation.

Möchte man von einem Date oder Timestamp jegliche Uhrzeitangabe abziehen, kann mit TRUNC gearbeitet werden. Das Resultat ist immer noch ein Date oder Timestamp, zu einem bestimmten Datum um 0:00:00 Uhr.


select trunc(localtimestamp) from dual; --heute um genau 0:00 Uhr

select trunc(localtimestamp) from dual; --heute um genau 0:00 Uhr

Neben vier oben genannten Datentypen für die Speicherung von Datum und Uhrzeit gibt es in Oracle zwei Zeitintervalldatentypen:

Datentyp Beschreibung

INTERVAL YEAR TO MONTH

Datentyp	Beschreibung
INTERVAL YEAR TO MONTH	Drückt den Intervall zwischen zwei Zeitpunkten in Jahren und Monaten aus. Bei der Deklaration, kann optional die Anzahl der möglichen Stellen der Jahreszahl (0 – 4) angegeben werden. Beispiel:`l_int1 INTERVAL YEAR(3) TO MONTH;` Dieser Intervall kann hier also max. 999 Jahre und 11 Monate betragen. Der Defaultwert für die Anzahl der möglichen Stellen der Jahreszahl ist 2.Eine Ausgabe hat das Format „yy-mm“ (bei 2 Stellen für die Anzahl der Jahre). Beispiel: „+12-01“ für 12 Jahre und ein Monat.
INTERVAL DAY TO SECOND	Drückt den Intervall zwischen zwei Zeitpunkten in Tagen, Stunden, Minuten, Sekunden und Bruchteilen von Sekunden aus. Bei der Deklaration, kann optional die Anzahl der möglichen Stellen der Tageszahl (0 – 9) und Bruchteile von Sekunden (0 – 9) angegeben werden. Beispiel:`l_int2 INTERVAL DAY(4) TO SECOND(6);` Dieser Intervall könnte somit max. 9999 Tage, 23 Stunden, 59 Minuten, 59 Sekunden und 999999 millionstel Sekunden betragen. Der Defaultwert für die Anzahl der möglichen Stellen der Tageszahl ist 2 und für die Bruchteile von Sekunden 6.Eine Ausgabe hat standardmäßig das Format: „dd hh:mm:ss.xxxxxx“Beispiel: „22 07:44:23.982710“ für 22 Tage, 7 Stunden, 44 Minuten, 23 Sekunden plus 0.982710 Sekunden.

Drückt den Intervall zwischen zwei Zeitpunkten in Jahren und Monaten aus. Bei der Deklaration, kann optional die Anzahl der möglichen Stellen der Jahreszahl (0 – 4) angegeben werden.

Beispiel:l_int1 INTERVAL YEAR(3) TO MONTH;

Dieser Intervall kann hier also max. 999 Jahre und 11 Monate betragen. Der Defaultwert für die Anzahl der möglichen Stellen der Jahreszahl ist 2.Eine Ausgabe hat das Format „yy-mm“ (bei 2 Stellen für die Anzahl der Jahre). Beispiel: „+12-01“ für 12 Jahre und ein Monat.

INTERVAL DAY TO SECOND

Drückt den Intervall zwischen zwei Zeitpunkten in Tagen, Stunden, Minuten, Sekunden und Bruchteilen von Sekunden aus. Bei der Deklaration, kann optional die Anzahl der möglichen Stellen der Tageszahl (0 – 9) und Bruchteile von Sekunden (0 – 9) angegeben werden.

Beispiel:l_int2 INTERVAL DAY(4) TO SECOND(6);

Dieser Intervall könnte somit max. 9999 Tage, 23 Stunden, 59 Minuten, 59 Sekunden und 999999 millionstel Sekunden betragen. Der Defaultwert für die Anzahl der möglichen Stellen der Tageszahl ist 2 und für die Bruchteile von Sekunden 6.Eine Ausgabe hat standardmäßig das Format:
„dd hh:mm:ss.xxxxxx“Beispiel: „22 07:44:23.982710“ für 22 Tage, 7 Stunden, 44 Minuten, 23 Sekunden plus 0.982710 Sekunden.

Für die Verwendung von Intervalldatentypen als Rückgabewerte von PL/SQL-Funktionen, bieten sich die internen Subdatentypen YMINTERVAL_UNCONSTRAINED und DSINTERVAL_UNCONSTRAINED an, die, wie der Name schon andeutet, die maximale Auflösung an Jahren bzw. Tagen und Sekunden besitzen.

Das folgende PL-/SQL-Beispiel gibt ein Zeitintervall im Format von den zwei verschiedenen Intervalldatentypen aus:


set serveroutput on
declare
   l_ts1 timestamp := localtimestamp;
   l_ts2 timestamp := localtimestamp + interval '20' day; -- jetzt + 20 Tage
   l_int1 interval year to month;
   l_int2 interval day to second;
begin
   l_int1 := (l_ts2 - l_ts1) year to month;
   l_int2 := (l_ts2 - l_ts1) day to second;
   dbms_output.put_line(l_int1);
   dbms_output.put_line(l_int2);
end;
/

set serveroutput on

declare

l_ts1 timestamp := localtimestamp;

l_ts2 timestamp := localtimestamp + interval '20' day; -- jetzt + 20 Tage

l_int1 interval year to month;

l_int2 interval day to second;

begin

l_int1 := (l_ts2 - l_ts1) year to month;

l_int2 := (l_ts2 - l_ts1) day to second;

dbms_output.put_line(l_int1);

dbms_output.put_line(l_int2);

end;

Mit der Syntax „year to month“ bzw. „day to second“ wird bei der Intervallberechnung definiert, welcher der beiden Intervalltypen es sein soll.

Mit der Intervall-Syntax lassen sich Literale unmittelbar in die gewünschten Intervalle übersetzen. Das Eingabeformat bei „day to second“ und „year to month“ ist hierbei, wie oben bereits erwähnt, fix vorgegeben:


select localtimestamp + interval '0 1:30:00.000' day to second in_anderthalb_stunden from dual;
select localtimestamp + interval '2-11' year to month zwei_jahr_11_monate from dual;

select localtimestamp + interval '0 1:30:00.000' day to second in_anderthalb_stunden from dual;

select localtimestamp + interval '2-11' year to month zwei_jahr_11_monate from dual;

Möglich sind auch folgende Arten der Intervallangaben:


select localtimestamp + interval '60' minute in_einer_stunde from dual;
select localtimestamp + interval '36' hour in_36_stunden from dual;
select localtimestamp + interval '36:30' hour to minute "in 36.5 stunden" from dual;
select localtimestamp - interval '1' day vor_einem_tag from dual; 
select localtimestamp + interval '11' month in_11_monaten from dual;

select localtimestamp + interval '60' minute in_einer_stunde from dual;

select localtimestamp + interval '36' hour in_36_stunden from dual;

select localtimestamp + interval '36:30' hour to minute "in 36.5 stunden" from dual;

select localtimestamp - interval '1' day vor_einem_tag from dual;

select localtimestamp + interval '11' month in_11_monaten from dual;

Eine weitere Möglichkeit, Strings in Intervalle zu konvertieren, bietet der Einsatz der Funktionen TO_DSINTERVAL bzw. TO_YMINREVAL, bei denen ebenfalls die Formatierung des Eingabestrings für den Intervall feststeht:


select localtimestamp + to_yminterval('01-02') from dual;
select localtimestamp + to_dsinterval('0 22:23:00.00') from dual;

select localtimestamp + to_yminterval('01-02') from dual;

select localtimestamp + to_dsinterval('0 22:23:00.00') from dual;

In Oracle bestand zudem schon immer die einfache Möglichkeit, Timestamps oder Dates mit einer definierten Anzahl von Tagen zu verrechnen:


select systimestamp + 5 from dual; -- aktueller Zeitpunkt + 5 Tage
select sysdate - 4.5 from dual; -- aktueller Zeitpunkt + 4,5 Tage

select systimestamp + 5 from dual; -- aktueller Zeitpunkt + 5 Tage

select sysdate - 4.5 from dual; -- aktueller Zeitpunkt + 4,5 Tage

Eine weitere Möglichkeit bietet sich bei Timestamps mit den Funktionen NUMTODSINTERVAL und NUMTOYMINTERVAL an:


select localtimestamp + numtodsinterval(22,'Day') from dual; 
select localtimestamp + numtodsinterval(85,'Minute') from dual; 
select localtimestamp + numtodsinterval(22,'Second') from dual; 
select localtimestamp + numtoyminterval(2,'Month') from dual; 
select localtimestamp + numtoyminterval(2,'Year') from dual;

select localtimestamp + numtodsinterval(22,'Day') from dual;

select localtimestamp + numtodsinterval(85,'Minute') from dual;

select localtimestamp + numtodsinterval(22,'Second') from dual;

select localtimestamp + numtoyminterval(2,'Month') from dual;

select localtimestamp + numtoyminterval(2,'Year') from dual;

Bei Dates gibt es die klassische Möglichkeit, mit der Funktion ADD_MONTHS eine bestimmte Anzahl von Monaten zu einem Datumswert zu addieren und mit MONTHS_BETWEEN die Anzahl von Monaten zwischen zwei Zeitpunkten zu ermitteln. Die ebenfalls praktische Funktion LAST_DAY ermittelt den letzten Tag im Monat des übergebenen Dates (bei gleichbleibender Uhrzeit):


select add_months(to_date('31.12.2013','dd.mm.yyyy'), 2) from DUAL;  --> 28.02.2014
select months_between(sysdate + 45, sysdate) from dual; --Achtung: späterer Zeitpunkt zuerst!
select last_day(sysdate) from dual;

select add_months(to_date('31.12.2013','dd.mm.yyyy'), 2) from DUAL; --> 28.02.2014

select months_between(sysdate + 45, sysdate) from dual; --Achtung: späterer Zeitpunkt zuerst!

select last_day(sysdate) from dual;

Die Funktion EXTRACT gestattet es, bestimmte Datumskomponenten aus einem Date oder Timestamp zu extrahieren:


select extract(year from systimestamp) from dual;
select extract(month from systimestamp) from dual;
select extract(day from systimestamp) from dual;
select extract(hour from systimestamp) from dual;
select extract(minute from systimestamp) from dual;
select extract(second from systimestamp) from dual;
select extract(timezone_hour from systimestamp) from dual;
select extract(timezone_minute from systimestamp) from dual;
select extract(timezone_region from systimestamp) from dual;
select extract(timezone_abbr from systimestamp) from dual;

select extract(year from systimestamp) from dual;

select extract(month from systimestamp) from dual;

select extract(day from systimestamp) from dual;

select extract(hour from systimestamp) from dual;

select extract(minute from systimestamp) from dual;

select extract(second from systimestamp) from dual;

select extract(timezone_hour from systimestamp) from dual;

select extract(timezone_minute from systimestamp) from dual;

select extract(timezone_region from systimestamp) from dual;

select extract(timezone_abbr from systimestamp) from dual;

EXTRACT ist auch auf Intervalle anwendbar, wie folgendes PL-/SQL-Beispiel zeigt:


set serveroutput on
declare
   l_ts1 timestamp := localtimestamp;
   l_ts2 timestamp := localtimestamp + numtodsinterval(4085,'Minute');
   l_int interval day to second;
begin
   l_int := (l_ts2 - l_ts1);  -- geht auch implizit ohne Anweisung "day to second" (s.o.)
   dbms_output.put_line(l_int);
   dbms_output.put_line('Der Intervall zwischen '||to_char(l_ts1, 'dd.mm.yyyy hh24:mi:ss.ff')
                        ||' und '||to_char(l_ts2, 'dd.mm.yyyy hh24:mi:ss.ff')||' betraegt: ');
   dbms_output.put_line(extract(day from l_int)||' Tage '||
                        extract(hour from l_int)||' Stunden '||
                        extract(minute from l_int)||' Minuten und '||
                        extract(second from l_int)||' Sekunden. ');
end;
/

set serveroutput on

declare

l_ts1 timestamp := localtimestamp;

l_ts2 timestamp := localtimestamp + numtodsinterval(4085,'Minute');

l_int interval day to second;

begin

l_int := (l_ts2 - l_ts1); -- geht auch implizit ohne Anweisung "day to second" (s.o.)

dbms_output.put_line(l_int);

dbms_output.put_line('Der Intervall zwischen '||to_char(l_ts1, 'dd.mm.yyyy hh24:mi:ss.ff')

||' und '||to_char(l_ts2, 'dd.mm.yyyy hh24:mi:ss.ff')||' betraegt: ');

dbms_output.put_line(extract(day from l_int)||' Tage '||

extract(hour from l_int)||' Stunden '||

extract(minute from l_int)||' Minuten und '||

extract(second from l_int)||' Sekunden. ');

end;

Soviel zur Schnelleinführung in das Thema Datums-, Zeit- und Intervalltypen bei Oracle. Man ahnt schon, dass das Thema vielfältig und nicht ohne Fallstricke ist. Ich hoffe, dass die obigen Ausführungen und Beispiele das Thema etwas transparenter gemacht haben.

Folgen

< Kommentare (2)

Beitrag kommentieren

Kommentare

19.05.2017 von Walter Hery
Hallo Herr Winter,

erstmal Vielen Dank für den interessanten Blog.

Ich hätte da aber noch ein Problem :-(

Gibt es eine Möglichkeit ein
früheres Datum von einem späteren Datum abzuziehen
und die Ausgabe soll eine (An)Zahl von Tagen ergeben.

Eine Ausgabe wie Anzahl_Monate und Anzahl_Tage,
oder Anzahl_Jahre Anzahl_monate Anzahl_Tage
wäre auch OK

Im Voraus schon mal Vielen Dank.

Gruß W. Hery
- 19.05.2017 von Frank Winter
  Hallo Herr Hery,
  nichts einfacher als das. Sie müssen nur das eine Datum von dem anderen subtrahieren. Hier ein Beispiel:
  
  select to_date('12.03.2017 12:45','dd.mm.yyyy hh24:mi') - to_date('12.02.2017 14:40','dd.mm.yyyy hh24:mi') from dual;
  
  Das Resultat ist die Anzahl an Tagen zwischen den beiden Datumsangaben. Enthalten die Datumsangaben verschiedene Uhrzeiten (wie in dem Beispiel), wird die Anzahl an Tagen inkl. Nachkommastellen ausgegeben.
  Gruß
  Frank Winter