Convert epoch to date in Oracle
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.999Incidentally, 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...)