Long to timestamp for historic data (pre-1900s)
- by Mike
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:
to_timestamp('1-JAN-1970 00:00:00', 'dd-mon-yyyy hh24:mi:ss') + numtodsinterval(int_to_convert/(1000),'SECOND' );
This wraps the date and we get timestamps 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.
