Convert epoch with milliseconds into Date format in PL/SQL

Question

I have a column in my table (Data_type Number(16,0)) which stores the epoch time with milliseconds.

Eg :- 1491456096759

I want to write a select statement to format this into readable format which includes milliseconds 'YYYY-MM-DD HH24:MI:SS.FF'.

Is there any direct functions that i can use for this conversion??

I have already tried this option

select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / 1000) * 1491456096759 from dual;

But not able to print that date in 'YYYY-MM-DD HH24:MI:SS.FF' format

Maybe this will be helpful: http://stackoverflow.com/questions/37305135/oracle-convert-unix-epoch-time-to-date — kpater87, May 04 '17 at 15:45
Possible duplicate of [oracle convert unix epoch time to date](http://stackoverflow.com/questions/37305135/oracle-convert-unix-epoch-time-to-date) — Sudipta Mondal, May 04 '17 at 15:52

kpater87 · Accepted Answer · 2017-05-05T06:10:47.993

Please try this:

SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0'
                   ,'YYYY-MM-DD HH24:MI:SS.FF'
       ) + NUMTODSINTERVAL(1493963084212/1000, 'SECOND')
FROM dual;

Or this if you want to return a string:

SELECT TO_CHAR( 
         TO_TIMESTAMP('1970-01-01 00:00:00.0'
                     ,'YYYY-MM-DD HH24:MI:SS.FF'
         ) + NUMTODSINTERVAL(1493963084212/1000, 'SECOND')
        ,'YYYY-MM-DD HH24:MI:SS.FF')
FROM dual;

Convert epoch with milliseconds into Date format in PL/SQL

1 Answers1