Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- prog:sql_date_oracle_rechnung [2020/11/23 20:22] – [Mit Zeitzonen arbeiten] gpipperr
+++ prog:sql_date_oracle_rechnung [2020/11/23 20:33] (aktuell) – [Unix Time / POSIX time / EPOCH] gpipperr
@@ Zeile 374: / Zeile 374: @@
 ----
-===== Unix EPOCH in Oracle Timestamp umwandeln =====
+===== 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
+---
+-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
+--
+-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 ) ;
-select (timestamp '1970-01-01 00:00:00 GMT' + numtodsinterval(1113135689000/1000, 'SECOND'))  at time zone 'Europe/Berlin'
-from dual
+---
+1606159849.703196
 </code>