===== 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 **[[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions060.htm|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 [[https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions092.htm|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
----
==== Quellen ====
* http://psoug.org/reference/date_func.html
* http://www.toadworld.com/platforms/oracle/w/wiki/2042.interval-arithmetic.aspx
* https://www.doag.org/konferenz/konferenzplaner/konferenzplaner_details.php?id=527880&locS=0&vid=534371