Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:sql_date_oracle_rechnung

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

Quellen

Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information
prog/sql_date_oracle_rechnung.txt · Zuletzt geändert: 2020/11/23 20:33 von gpipperr