历史数据的长时间戳（1900 年代之前）

发布于 2024-09-02 05:03:42 字数 510 浏览 1 评论 0原文

我有一个开始和停止时间的数据库，以前都有相当新的数据（1960 年代至今），我可以存储很长的整数。这与unix时间戳非常相似，只有毫秒精度，因此像java.util.Date.getTime()这样的函数将是当前时间的值。

到目前为止，这种方法运行良好，但我们最近获得了 1860 年代的数据，当导致 times < 的值时，以下代码不再起作用。 1901（给出或接受）：

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

尝试使用以毫秒为单位的值（例如 -2177452800000）会导致一些问题，例如返回带有 2038 年时间戳的日期。有解决此问题的方法吗？我查看过的所有文档和时间戳都应该能够处理一直到 -4000 (BC) 的年份，所以我怀疑 numtodsinterval 存在问题。

任何想法建议将不胜感激。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

水中月 2024-09-09 05:03:42

像这样的事情怎么样：

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);

分离出 DAY 部分并添加整天，然后取余数并以更高的精度添加

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

回复收藏 0 原文