prog:sql_date_oracle_rechnung
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende ÜberarbeitungLetzte ÜberarbeitungBeide Seiten der Revision | ||
prog:sql_date_oracle_rechnung [2018/02/13 11:18] – [Literal Angabe Date] gpipperr | prog:sql_date_oracle_rechnung [2020/11/23 20:32] – [Unix Time / POSIX time / EPOCH] 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> | ||
+ | |||
+ | DUMP(DATE' | ||
+ | ------------------------------------------------------- | ||
+ | Typ=13 Len=8: 225, | ||
+ | |||
+ | select dump(TIMESTAMP' | ||
+ | |||
+ | DUMP(TIMESTAMP' | ||
+ | ----------------------------------------------------------------------- | ||
+ | Typ=187 Len=20: 225, | ||
+ | |||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Literal Angabe Date ==== | ||
+ | |||
+ | Bei der Literal Angabe eines Datums wird fest für Date das Format ' | ||
+ | |||
+ | DATE | ||
+ | <code sql> | ||
+ | select DATE' | ||
+ | |||
+ | DATE' | ||
+ | -------------- | ||
+ | 31.12.17 00:00 | ||
+ | |||
+ | </ | ||
+ | |||
+ | TIMESTAMP | ||
+ | <code sql> | ||
+ | select TIMESTAMP' | ||
+ | |||
+ | TIMESTAMP' | ||
+ | ---------------------------------------------- | ||
+ | 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 " | ||
+ | |||
+ | Beispiel: | ||
+ | <code sql> | ||
+ | SQL> select extract(day from sysdate), | ||
+ | |||
+ | EXTRACT(DAYFROMSYSDATE) EXTRACT(MONTHFROMSYSDATE) EXTRACT(YEARFROMSYSDATE) | ||
+ | ----------------------- ------------------------- ------------------------ | ||
+ | | ||
+ | </ | ||
+ | |||
+ | 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:// | ||
+ | |||
+ | |||
+ | 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 | ||
+ | </ | ||
+ | |||
+ | Lösung, systimestamp verwenden: | ||
+ | <code sql> | ||
+ | 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. | ||
+ | |||
+ | <code sql> | ||
+ | select sysdate +1 from dual; | ||
+ | |||
+ | SYSDATE+1 | ||
+ | -------------- | ||
+ | 12.05.17 19:34 | ||
+ | |||
+ | select to_date(to_char(DATE' | ||
+ | |||
+ | TO_DATE(TO_CHA | ||
+ | -------------- | ||
+ | 01.01.18 12:56 | ||
+ | |||
+ | -- Bruchteile 1/24/60 => 1 Minute | ||
+ | |||
+ | -- 30 Minuten hinzufügen | ||
+ | |||
+ | select sysdate + ((1/ | ||
+ | |||
+ | SYSDATE+((1/ | ||
+ | -------------- | ||
+ | 11.05.17 20:06 | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | ====Erste Montag in der aktuellen Woche ==== | ||
+ | |||
+ | Nächten Montag holen und 7 Tage zurück rechnen. | ||
+ | |||
+ | <code sql> | ||
+ | select next_DAY(sysdate,' | ||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | ====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 | ||
+ | | ||
+ | | ||
+ | is | ||
+ | | ||
+ | return ( p_d + (7*p_w) ) ; | ||
+ | end; | ||
+ | select add_weeks(sysdate, | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | ====Monate hinzufügen==== | ||
+ | |||
+ | |||
+ | Mit der **add_months** Funktion lassen sich Monate hinzufügen | ||
+ | |||
+ | z.B. ein Monat: | ||
+ | <code sql> | ||
+ | select add_months(sysdate, | ||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ====Tage von einander abziehen==== | ||
+ | |||
+ | Bei der Verwendung des Datentyps " | ||
+ | |||
+ | Um die Differenz zwischen zwei Zeitpunkten zu berechnen, genügt es das Ergebnis * den entsprechenden Teiler zu nehmen | ||
+ | |||
+ | Date1 - Date 2 = DIFFERENZ | ||
+ | |||
+ | |||
+ | <code sql> | ||
+ | select (sysdate-(sysdate-1/ | ||
+ | |||
+ | | ||
+ | ------------ | ||
+ | 720 | ||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ====Das höchsten Wert in drei Datumsfeldern ermitteln ==== | ||
+ | |||
+ | Am einfachsten mit der SQL Funktion **[[http:// | ||
+ | |||
+ | <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 | ||
+ | ------------------------ -------- -------- -------- -------- | ||
+ | A Column is the greates | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | ====Adventsrätsel - Wann fällt Nikolaus auf einen Sonntag? ==== | ||
+ | |||
+ | Liste alle Sonntag, dem 06.12 der letzten 150 Jahre auf: | ||
+ | |||
+ | <code sql> | ||
+ | select dat, | ||
+ | from ( | ||
+ | select to_date(' | ||
+ | ) | ||
+ | where to_char(dat,' | ||
+ | -- alternative (Sonntag mit mit leerzeichen ausgegeben! | ||
+ | --where trim(to_char(dat,' | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ====== Der Datentyp Timestamp ===== | ||
+ | |||
+ | |||
+ | Sehr gerne sieht man diese Meldung nicht ... => **" | ||
+ | |||
+ | Wie kann ich nun mit dem Timestamp Datenformat rechnen? | ||
+ | |||
+ | ====Aktuellen Zeitpunkt ausgeben ==== | ||
+ | |||
+ | <code sql> | ||
+ | select systimestamp | ||
+ | </ | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ====Tage von einem Timestamp abziehen ==== | ||
+ | |||
+ | |||
+ | Beispiel: Zwei Tage abziehen | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | select systimestamp - interval ' | ||
+ | |||
+ | </ | ||
+ | |||
+ | Es können auch mit " | ||
+ | |||
+ | ABER! | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | select timestamp' | ||
+ | |||
+ | TIMESTAMP' | ||
+ | --------------------------------------------------------------------------- | ||
+ | 01-FEB-17 11.00.00.000000000 PM | ||
+ | |||
+ | |||
+ | select timestamp' | ||
+ | |||
+ | select timestamp' | ||
+ | * | ||
+ | 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==== | ||
+ | |||
+ | |||
+ | <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 sql> | ||
+ | |||
+ | CREATE OR REPLACE FUNCTION intervalToSeconds( | ||
+ | | ||
+ | |||
+ | 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:// | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ===== Unix Time / POSIX time / EPOCH ===== | ||
+ | |||
+ | |||
+ | |||
+ | In Oracle Timestamp umwandeln: | ||
+ | <code sql> | ||
+ | |||
+ | SELECT (TIMESTAMP ' | ||
+ | |||
+ | --- | ||
+ | |||
+ | 23-NOV-20 08.22.52.000000000 PM EUROPE/ | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | 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 | ||
+ | |||
+ | |||
+ | --- | ||
+ | |||
+ | 1606159849.703196 | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | |||
+ | ==== Quellen ==== | ||
+ | |||
+ | * http:// | ||
+ | * http:// | ||
+ | * https:// | ||
+ | |||
prog/sql_date_oracle_rechnung.txt · Zuletzt geändert: 2020/11/23 20:33 von gpipperr