Inhaltsverzeichnis
Oracle DATE und TIMESTAMP - Rechnen in SQL und PL/SQL mit Datumsangaben
Das Rechnen mit Datumsangaben ist in SQL je nach Datentyp recht einfach.
Wichtig ist aber zu beachten, dass der Oracle Date Datentyp IMMER auch die Uhrzeit enthält!
Der Werte Bereich für eine Datum für DATE und TIMESTAMP liegt zwischen dem 01.01.-4713 und dem 31.12.9999.
Interne Darstellung
Oracle speichert intern das Datum in Dezimalstellen.
Dumpen der internen Darstellung mit der dump Funktion:
SYS@GPI-saturn>SELECT dump(DATE'2017-12-31') FROM dual; DUMP(DATE'2017-12-31') ------------------------------------------------------- Typ=13 Len=8: 225,7,12,31,0,0,0,0 SELECT dump(TIMESTAMP'2017-12-31 23:59:59') FROM dual; DUMP(TIMESTAMP'2017-12-3123:59:59') ----------------------------------------------------------------------- Typ=187 Len=20: 225,7,12,31,23,59,59,0,0,0,0,0,0,0,3,0,0,0,0,0
Literal Angabe Date
Bei der Literal Angabe eines Datums wird fest für Date das Format 'YYYY-MM-DD' und für Timestamp das Format 'YYYY-MM-DD hh24:mi:ss' verwendet.
DATE
SELECT DATE'2017-12-31' FROM dual; DATE'2017-12-3 -------------- 31.12.17 00:00
TIMESTAMP
SELECT TIMESTAMP'2017-12-31 23:59:59' FROM dual; TIMESTAMP'2017-12-3123:59:59' ---------------------------------------------- 31-DEC-17 11.59.59.000000000 PM
Rechnen mit dem Datentyp DATE
Wie kann nun aber mit den Datumswerten gerechnet werden?
Extract -- Einzelne Elemente aus einem Datum auslesen
Mit „EXTRACT“ können einzelne Elemente aus einem Datum ausgelesen werden.
Beispiel:
SQL> SELECT EXTRACT(DAY FROM sysdate),EXTRACT(MONTH FROM sysdate),EXTRACT(YEAR FROM sysdate) FROM dual; EXTRACT(DAYFROMSYSDATE) EXTRACT(MONTHFROMSYSDATE) EXTRACT(YEARFROMSYSDATE) ----------------------- ------------------------- ------------------------ 13 2 2018
Die Uhrzeit kann aber nicht aus einen DATE ausgelesen werden! EXTRACT ist ANSI Konform und unter ANSI SQL enthält Date keine Uhrzeit!
siehe https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm
ORA-30076: invalid extract field for extract source:
SQL> SELECT EXTRACT(HOUR FROM sysdate) FROM dual; SELECT EXTRACT(HOUR FROM sysdate) FROM dual * ERROR at line 1: ORA-30076: invalid EXTRACT FIELD FOR EXTRACT SOURCE
Lösung, systimestamp verwenden:
SQL> SELECT EXTRACT(HOUR FROM systimestamp) FROM dual; EXTRACT(HOURFROMSYSTIMESTAMP) ----------------------------- 10
Aktuelles Datum der Datenbank abfragen - sysdate versus CURRENT_DATE
sysdate
Gibt die aktuelle Zeit des Betriebssystems wieder,auf dem die Datenbank läuft
current_date
Gibt die aktuelle Uhrzeit für die angemeldete Session aus.
Tage bzw. Bruchteile von Tagen zu einem Datum hinzufügen
Um eine Tag zu einem Datum hinzuzufügen + 1 dazu zählen.
SELECT sysdate +1 FROM dual; SYSDATE+1 -------------- 12.05.17 19:34 SELECT to_date(to_char(DATE'2017-12-31','dd.mm.rr')||' 12:56')+1 FROM dual; TO_DATE(TO_CHA -------------- 01.01.18 12:56 -- Bruchteile 1/24/60 => 1 Minute -- 30 Minuten hinzufügen SELECT sysdate + ((1/24/60)*30) FROM dual; SYSDATE+((1/24 -------------- 11.05.17 20:06
Erste Montag in der aktuellen Woche
Nächten Montag holen und 7 Tage zurück rechnen.
SELECT next_DAY(sysdate,'MON')-7 FROM dual;
Wochen zu einem Datum hinzufügen
Entsprechende Tage hinzufügen
Hier ein Beispiel für 12c mit einer eigenen Funktion im With Block:
WITH FUNCTION add_weeks(p_d DATE,p_w NUMBER) RETURN DATE IS BEGIN RETURN ( p_d + (7*p_w) ) ; END; SELECT add_weeks(sysdate,4) FROM dual /
Monate hinzufügen
Mit der add_months Funktion lassen sich Monate hinzufügen
z.B. ein Monat:
SELECT add_months(sysdate,2) FROM dual;
Tage von einander abziehen
Bei der Verwendung des Datentyps „Date“ ist ein Tag genau 1, d. h. ein halber Tag ist 1/2, eine Stunde 1/24, eine Minute 1/(24*60)
Um die Differenz zwischen zwei Zeitpunkten zu berechnen, genügt es das Ergebnis * den entsprechenden Teiler zu nehmen
Date1 - Date 2 = DIFFERENZ ⇒ in Minuten DIFFERENZ / ( 1/(24*60)) ⇒ in Sekunden DIFFERENZ / (1/(24*60*60))
SELECT (sysdate-(sysdate-1/2) ) / ( 1/(24*60)) AS min_dif FROM dual; MIN_DIF ------------ 720
Das höchsten Wert in drei Datumsfeldern ermitteln
Am einfachsten mit der SQL Funktion GREATEST :
SELECT CASE WHEN the_high_value = a THEN 'A Column is the greates' WHEN the_high_value = b THEN 'B Column is the greatest' ELSE 'C Column is the greatest' END , the_high_value , a , b , c FROM (SELECT greatest (nvl (a, sysdate - 10000) , nvl (b, sysdate - 10000) , nvl (c, sysdate - 10000) ) the_high_value , a , b , c FROM (SELECT sysdate + dbms_random.value (1, 100) a , sysdate + dbms_random.value (1, 100) b , sysdate + dbms_random.value (1, 100) c FROM dual)) / COLUMN_NAME THE_HIGH A B C ------------------------ -------- -------- -------- -------- A COLUMN IS the greates 15.12.15 15.12.15 08.10.15 14.10.15
Adventsrätsel - Wann fällt Nikolaus auf einen Sonntag?
Liste alle Sonntag, dem 06.12 der letzten 150 Jahre auf:
SELECT dat,to_char(dat,'Day') FROM ( SELECT to_date('06.12.'|| to_char(1899+rownum)) AS dat FROM all_objects WHERE rownum < 150 ) WHERE to_char(dat,'Day') LIKE 'S_n%' -- alternative (Sonntag mit mit leerzeichen ausgegeben! --where trim(to_char(dat,'Day')) = 'Sonntag' /
Der Datentyp Timestamp
Sehr gerne sieht man diese Meldung nicht … ⇒ „ORA-00932: Inkonsistente Datentypen: INTERVAL DAY TO SECOND erwartet, NUMBER erhalten“ …
Wie kann ich nun mit dem Timestamp Datenformat rechnen?
Aktuellen Zeitpunkt ausgeben
SELECT systimestamp FROM dual;
Tage von einem Timestamp abziehen
Beispiel: Zwei Tage abziehen
SELECT systimestamp - INTERVAL '2' DAY FROM dual;
Es können auch mit „year“,„month“,„day“,„hour“,„minute“,„second“ entsprechende Zeiten abgezogen werden.
ABER!
SELECT TIMESTAMP'2017-01-01 23:00:00' + INTERVAL '1' MONTH FROM dual; TIMESTAMP'2017-01-0123:00:00'+INTERVAL'1'MONTH --------------------------------------------------------------------------- 01-FEB-17 11.00.00.000000000 PM SELECT TIMESTAMP'2017-01-31 23:00:00' + INTERVAL '1' MONTH FROM dual; SELECT TIMESTAMP'2017-01-31 23:00:00' + INTERVAL '1' MONTH FROM dual * ERROR at line 1: ORA-01839: DATE NOT valid FOR MONTH specified => KEIN BUG, so ist das IN Standard SQL spezifiziert!
Anzahl der Sekunden zwischen zwei Zeitpunkten berechnen
SELECT abs( EXTRACT( SECOND FROM interval_difference ) + EXTRACT( MINUTE FROM interval_difference ) * 60 + EXTRACT( HOUR FROM interval_difference ) * 60 * 60 + EXTRACT( DAY FROM interval_difference ) * 60 * 60 * 24 ) FROM ( SELECT systimestamp - (systimestamp - 1) AS interval_difference FROM dual )
CREATE OR REPLACE FUNCTION intervalToSeconds( p_Minuend TIMESTAMP , p_Subtrahend TIMESTAMP ) RETURN NUMBER IS v_Difference INTERVAL DAY TO SECOND ; v_Seconds NUMBER ; BEGIN v_Difference := p_Minuend - p_Subtrahend ; SELECT EXTRACT( DAY FROM v_Difference ) * 86400 + EXTRACT( HOUR FROM v_Difference ) * 3600 + EXTRACT( MINUTE FROM v_Difference ) * 60 + EXTRACT( SECOND FROM v_Difference ) INTO v_Seconds FROM DUAL ; RETURN v_Seconds ; END intervalToSeconds ;
Mit Zeitzonen arbeiten
Mit der Funktion NEW_TIME lässt sich eine Zeit in einer anderen Zeitzone berechnen.
Unix Time / POSIX time / EPOCH
In Oracle Timestamp umwandeln:
SELECT (TIMESTAMP '1970-01-01 00:00:00 GMT' + numtodsinterval(1606159372000/1000, 'SECOND' ) ) at TIME zone 'Europe/Berlin' FROM dual --- 23-NOV-20 08.22.52.000000000 PM EUROPE/BERLIN --- -- falls format mit . wie aus Python vorliegt: SELECT (TIMESTAMP '1970-01-01 00:00:00 GMT' + numtodsinterval(1606159849.703196, 'SECOND' ) ) at TIME zone 'Europe/Berlin' FROM dual -- 23-NOV-20 08.30.49.703196000 PM EUROPE/BERLIN
Oracle Timestamp in Epoch umwandeln:
SELECT abs( EXTRACT( SECOND FROM interval_difference ) + EXTRACT( MINUTE FROM interval_difference ) * 60 + EXTRACT( HOUR FROM interval_difference ) * 60 * 60 + EXTRACT( DAY FROM interval_difference ) * 60 * 60 * 24 ) FROM ( SELECT ( systimestamp - (TIMESTAMP '1970-01-01 00:00:00 GMT') ) AS interval_difference FROM dual ) ; --- 1606159849.703196