CODEWITHSUNDEEP
oracle-database
Sun Su
Sun Su

Reputation: 33

Convert epoch to date in Oracle

Was wondering if anyone could help with precision time conversion. Sample: 1501646399999 which is GMT: Wednesday, August 2, 2017 3:59:59.999 AM

I used the below query, but it always rounds off to 02-AUG-17 04:00:00. Can anyone please guide me

select TO_TIMESTAMP('1970-01-01 00:00:00.000', 'YYYY-MM-DD hh24:mi:SS.FF3') + ((1/86400000) * 1501646399999)
from dual;

Upvotes: 3

Views: 8848

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

The problem is that you're adding a number to your fixed timestamp, which is causing that timestamp to be implicitly converted to a date - which doesn't have sub-second precision.

If you add an interval instead then it stays as a timestamp:

alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF3';

select TO_TIMESTAMP('1970-01-01 00:00:00.000', 'YYYY-MM-DD hh24:mi:SS.FF3')
  + numtodsinterval(1501646399999/1000, 'SECOND')
from dual;

TO_TIMESTAMP('1970-01-0
-----------------------
2017-08-02 03:59:59.999

Incidentally, you could slightly simplify your query with a timestamp literal:

select TIMESTAMP '1970-01-01 00:00:00' + numtodsinterval(...)

You may also want to check if you should be declaring that timestamp as being UTC, and converting back to local time zone after adding the epoch value; or leaving it explicitly as UTC but as a timestamp with time zone value. It depends exactly what that number is supposed to represent. (You said it's GMT/UTC, but still...)

Upvotes: 2

Related Questions