Oracle SQL Date为Long，反之亦然 [英] Oracle SQL Date to Long and vice versa

问题描述

我有以下SQL Query将长整数转换为日期，我无法将日期转换回长数字。请您直接告知我您的建议。

I have the following SQL Query which converts the long number to a date , i cannot convert the date back to the long number . kindly let me know your suggestion.

SELECT to_date((( 1432550197431912935  - POWER(2,60)) /POWER(2,44)) + to_date('01-JAN-1970','DD-MON-YYYY')) from dual

输出

7/9/2013

Output

7/9/2013

select     TO_CHAR(((TO_DATE ('09-JUL-2013','DD-MON-YYYY') -to_date('01-JAN-1970','DD-MON-YYYY'))  * POWER(2,44) ) + POWER(2,60)) from dual

输出

1432549301782839296

长值不一样。

推荐答案

您在转换中的精度太低，以能够以其他方式回溯。您可以使用时间戳而不是日期来更近。

You are losing too much precision in your conversion to be able to go back the other way. You can get closer by using timestamps instead of dates.

首先，您的初始查询完全失去了时间部分：

Firstly your initial query was losing the time component completely:

select to_char(date '1970-01-01'
  + (1432550197431912935 - power(2, 60))/power(2, 44), 'YYYY-MM-DD HH24:MI:SS')
from dual;

2013-07-09 01:13:19

...但是即使是这样，转换已经失去了太多的价值：

... but even with that, converting back has lost way too much:

select ((to_date('2013-07-09 01:13:19','YYYY-MM-DD HH24:MI:SS')
  - date '1970-01-01') * power(2, 44)) + power(2, 60) from dual;

1432550197477589405

哪个比您所得到的1432549301782839296更接近，但还是相当一个很长的路要走。

Which is closer than the 1432549301782839296 you got, but still quite a long way off.

部分问题是 DATE 的精度，这只是第二个。如果您使用 TIMESTAMP ，则可以相当接近;你可以看到有价值是非常精确的：

Part of the problem is the precision of DATE, which is only to the second. If you use TIMESTAMP instead you can get quite close; you can see that the value to have is supposedly very precise:

select timestamp '1970-01-01 00:00:00'
  + numtodsinterval((1432550197431912935 - power(2, 60))/power(2, 44), 'DAY')
from dual;

2013-07-09 01:13:18.775670462

转换回通过时间戳算法给出间隔结果是复杂的，然后您需要操作以返回一个数字，首先是原始天数：

Converting that back is complicated by timestamp arithmetic giving interval results, which you then have to manipulate to get back to a number, first as the original number of days:

select extract(day from int_val)
  + extract(hour from int_val) / 24
  + extract(minute from int_val) / (24 * 60)
  + extract(second from int_val) / (24 * 60 * 60)
from (
select to_timestamp('2013-07-09 01.13.18.775670462', 'YYYY-MM-DD HH24:MI:SS.FF9')
  - timestamp '1970-01-01 00:00:00' as int_val from dual);

15895.0509117554451620370370370370370371

...然后用你的权力操纵： p>

... and then with your power manipulation:

select ((extract(day from int_val)
    + extract(hour from int_val) / 24
    + extract(minute from int_val) / (24 * 60)
    + extract(second from int_val) / (24 * 60 * 60))
  * power(2, 44)) + power(2, 60)
as x
from (
select to_timestamp('2013-07-09 01.13.18.775670462', 'YYYY-MM-DD HH24:MI:SS.FF9')
  - timestamp '1970-01-01 00:00:00' as int_val from dual);

1432550197431912935.09988554676148148148

哪个很贴心。您可以将其截断或缩小到最接近的整数。

Which is pretty darn close. You could trunc or round that to the nearest whole number.

只需查看您的数字和功能操作，每种方式显示这似乎在Oracle能够应付的精度之内：

Just looking at your numbers and the power manipulation each way shows that it seems to be within the precision Oracle can cope with:

select (1432550197431912935 - power(2, 60)) / power(2, 44)
from dual;

15895.050911755445156359201064333319664

select (15895.050911755445156359201064333319664 * power(2, 44)) + power(2, 60)
from dual;

1432550197431912935.000...

即使有时间戳，您会失去一些因为第一个值超过了9位数的小数第二个限制。代表分数秒的部分 - 一旦你考虑到15895小时等等 - 是一天的 .0000089776673785814232865555418862 ，这是 .77567046150943497195839881896768 秒;时间戳是四舍五入到 .775670462 。所以它永远不会完美。

Even with a timestamp, you lose some of that, as that first value is going past the 9-digit fractional second limit. The part that represents the fractional seconds - once you've accounted for the 15895 hours etc. - is .0000089776673785814232865555418862 of a day, which is .77567046150943497195839881896768 seconds; the timestamp is rounding that to .775670462. So it's never going to be perfect.

这也导致人们想知道原始数字是如何生成的;它似乎不太可能代表一个时间到极限的精度，因为它不在 yoctoseconds 。 精确度实际上是否是基于2的权力被操纵的人造物，这不是很清楚，但是它看起来并不是很有用。使用Unix风格的纪元日期更常见，它计数秒或有时毫秒，因为你使用的时代，如果它必须存储为一个数字。这个设计很有意思。

That also leads one to wonder how the original number is being generated; it seems unlikely it actually represents a time down to that extreme precision, since it's below yoctoseconds. It isn't really clear if the 'precision' is actually an artefact of it being manipulated based on powers of 2, but it doesn't look very useful anyway. It's more common to use the Unix-style epoch date, counting seconds or sometimes milliseconds since the epoch date you're using anyway, if it has to be stored as a number at all. This design is... interesting.

这篇关于Oracle SQL Date为Long，反之亦然的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！