Long to timestamp for historic data (pre-1900s)

Question

I have a database of start and stop times that have previously all had fairly recent data (1960s through present day) which i've been able to store as long integers. This is very simialr to unix timestamps, only with millisecond precision, so a function like java.util.Date.getTime() would be the value of the current time.

This has worked well so far, but we recently got data from the 1860s, and the following code no longer works when values that result in times < 1901 (give or take):

to_timestamp('1-JAN-1970 00:00:00', 'dd-mon-yyyy hh24:mi:ss') + numtodsinterval(int_to_convert/(1000),'SECOND' );

trying this with a value in milliseconds such as -2177452800000 causes some issues, such as returning the date with a timestamp in the year 2038. Is there a way around this issue? All of the documentation i've looked at the documentation and timestamps should be able to handle years all the way back to the -4000 (BC), so i'm suspecting an issue with the numtodsinterval.

Any ideas suggestions would be greatly appreciated.

Answer 1

How about select to_date('01-JAN-1970','DD-MON-YYYY') + ( -1111111111 / (60 * 60 * 24*1000) ) from dual; That's what I use to convert Java milliseconds to dates.

Answer 2

How about something like this :

select to_timestamp('1-JAN-1970 00:00:00', 'dd-mon-yyyy hh24:mi:ss') +
         numtodsinterval(val /(1000*60*60*24),'DAY' ) +
         numtodsinterval(
           ((val /(1000*60*60*24)) - (trunc(val /(1000*60*60*24))) ) * 60*60*24,'SECOND')
from (select -2177452812340 val from dual);

Separate out the DAY component and add the whole days, then take the remainder and add that at the higher precision