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

Answer 1

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;