Gunther Oracle APEX und Datenbank Wiki

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Zuletzt angesehen:
prog:sql_date_oracle_rechnung

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
Nächste ÜberarbeitungBeide Seiten der Revision
prog:sql_date_oracle_rechnung [2018/02/13 11:18] – [Extract -- Werte aus dem Daten auslesen] gpipperrprog:sql_date_oracle_rechnung [2018/02/13 11:28] – gpipperr
Zeile 1: Zeile 1:
 +===== 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.
 +
 +----
 +
 +
 +
 +----
 +
 +
 +
 +==== 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
 +
  
prog/sql_date_oracle_rechnung.txt · Zuletzt geändert: 2020/11/23 20:33 von gpipperr