Translate UNIX / Linux EPOCH time to Oracle date

Epoch time in computers is general defined as the number of milliseconds since 1 January 1970 and provides for a very useful way of determining times between system and other time specific functions.   However, it is very difficult for humans to read.   The following code will convert an Epoch time to Oracle date time format.

Given epoch time is 579052800000. we can retrieve oracle time as below.

SQL> define test_time=579052800000

SQL> select  (TO_DATE(’01-01-1970′,’MM-DD-YYYY’) + (&test_time / 86400000)-1) from dual;

old   1: select  (TO_DATE(’01-01-1970′,’MM-DD-YYYY’) + (&test_time / 86400000)-1) from dual

new   1: select  (TO_DATE’01-01-1970′,’MM-DD-YYYY’) + (579052800000 / 86400000)-1) from dual

(TO_DATE(

———

07-MAY-88

SQL>

and convert standard oracle date to epoch

 

select to_char(sysdate – to_date(’01-01-1970′,’MM-DD-YYYY’))*86400000 from dual;

 

SQL> select to_char(sysdate – to_date(’01-01-1970′,’MM-DD-YYYY’))*86400000 from dual;

TO_CHAR(SYSDATE-TO_DATE(’01-01-1970′,’MM-DD-YYYY’))*86400000 ————————————————————

1.3807E+12

SQL>

Larry Catt