Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
===== 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:
<code sql>
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

</code>

----

==== 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
<code sql>
select DATE'2017-12-31' from dual;

DATE'2017-12-3
--------------
31.12.17 00:00

</code>

TIMESTAMP
<code sql>
select TIMESTAMP'2017-12-31 23:59:59' from dual;

TIMESTAMP'2017-12-3123:59:59'
----------------------------------------------
31-DEC-17 11.59.59.000000000 PM

</code>




----


===== 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:
<code sql>
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
</code>

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:
<code sql>
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
</code>

Lösung, systimestamp verwenden:
<code sql>
SQL> select extract(hour from systimestamp) from dual;

EXTRACT(HOURFROMSYSTIMESTAMP)
-----------------------------
                           10

</code>


----

====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.

<code sql>
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

</code>


----


====Erste Montag in der aktuellen Woche ====

Nächten Montag holen und 7 Tage zurück rechnen.

<code sql>
select next_DAY(sysdate,'MON')-7 from dual; 
</code>

----


====Wochen zu einem Datum hinzufügen====

Entsprechende Tage hinzufügen

Hier ein Beispiel für 12c mit einer eigenen Funktion im With Block:

<code sql>

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
/
</code>

----


====Monate hinzufügen====


Mit der **add_months** Funktion lassen sich Monate hinzufügen

z.B. ein Monat:
<code sql>
select add_months(sysdate,2)  from dual;
</code>

----

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


<code sql>
select (sysdate-(sysdate-1/2) )  /  ( 1/(24*60)) as min_dif from dual;

     MIN_DIF
------------
         720
</code>

----

====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]] **:

<code sql>
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                  

</code>


----


====Adventsrätsel - Wann fällt Nikolaus auf einen Sonntag? ====

Liste alle Sonntag, dem 06.12 der letzten 150 Jahre auf:

<code sql>
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'
/
</code>

----

----

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

<code sql>
select systimestamp  from dual;
</code>


----

====Tage von einem Timestamp abziehen ====


Beispiel: Zwei Tage abziehen

<code sql>

select systimestamp - interval '2' day from dual;

</code>

Es können auch mit "year","month","day","hour","minute","second" entsprechende Zeiten abgezogen werden.

ABER!

<code sql>

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!
</code>

----

====Anzahl der Sekunden zwischen zwei Zeitpunkten berechnen====


<code sql>
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 )
		   
</code>

<code sql>		   

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 ; 

</code>


----

=====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: 
<code sql>

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

</code>


Oracle Timestamp in Epoch umwandeln: 

<code sql>
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

</code>




----



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