PLSQL - convert unix timestamp with millsecond precision to timestamp(6)

Question

I have a unix timstamp with millsecond precision like below:

1523572200000

I need to convert it to timestamp(6). This is the format I need:

05-NOV-14 09.45.00.000000000 AM

(Fyi examples above are not matching dates, just using as example.)

What's the best way to go about this?

Thanks!

Answer 1

Assuming that current timestamp is: 1523572200000, try following:

select cast (to_date('1970-01-01', 'YYYY-MM-DD') + 1523572200000/1000/60/60/24 as timestamp) from dual;

where:

• to_date('1970-01-01', 'YYYY-MM-DD') is epoch time
• <unix_timestamp>/60/60/24 was divided by 1000 miliseconds 60 second and 60 minutes and 24 hours because in oracle we are adding days