single.php
< Beitrag von Frank Winter

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:

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:

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:

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:

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.

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.

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 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:

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:

Möglich sind auch folgende Arten der Intervallangaben:

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:

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

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

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):

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

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

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
X

Folgen

E-mail : *
Kategorie: Oracle-Development | Schlagwörter: , , , , | Kommentare: 2

Beitrag kommentieren

CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.

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