Oracle 日期舍入

Oracle Date Rounding

提问by YogoZuno

This call -

这个电话——

SELECT ((TO_DATE ('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS') - TO_DATE ('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS')) * 1440.000) from dual

SELECT ((TO_DATE ('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS') - TO_DATE ('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS')) * 1440.000) from dual

Produces a result of 95 - which is what you would expect from the calculation.

产生 95 的结果 - 这是您从计算中期望的结果。

This call -

这个电话——

SELECT trunc((TO_DATE ('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS') - TO_DATE ('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS')) * 1440.000) from dual

SELECT trunc((TO_DATE ('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS') - TO_DATE ('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS')) * 1440.000) from dual

produces a result of 94.

产生 94 的结果。

The suggested fix is - SELECT trunc((TO_DATE ('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS') - TO_DATE ('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS')) * 1440.000+.00001) from dual

建议的修复是 - SELECT trunc((TO_DATE ('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS') - TO_DATE ('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS')) * 1440.000+.00001) from dual

This solution seems wrong to me - can anyone suggest a better option? The actual requirement is to count the whole minutes of difference between two dates.

这个解决方案对我来说似乎是错误的 - 任何人都可以提出更好的选择吗？实际要求是计算两个日期之间的整分钟差异。

回答by Jeffrey Kemp

Excellent question.

很好的问题。

The date arithmetic is not entirely accurate due to datatype conversions here.

由于此处的数据类型转换，日期算术并不完全准确。

TO_DATE('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS')
- TO_DATE('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS'))
= .0659722222222222222222222222222222222222000000000000000

But

但

.0659722222222222222222222222222222222222000000000000000
* 1440
=94.9999999999999999999999999999999999999700000000000

Which means that none of TRUNC, ROUND, CEIL and FLOOR will work in all cases.

这意味着 TRUNC、ROUND、CEIL 和 FLOOR 都不适用于所有情况。

To solve this problem, you need to be able to convert each date to an integer before performing arithmetic on it, e.g.:

要解决这个问题，您需要能够在对每个日期执行算术运算之前将其转换为整数，例如：

select FLOOR((TO_CHAR(TO_DATE('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS'),'J') * 1440
+ TO_CHAR(TO_DATE('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS'),'SSSSS') / 60)
- (TO_CHAR(TO_DATE('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS'),'J') * 1440
+ TO_CHAR(TO_DATE('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS'),'SSSSS') / 60))
from dual;

回答by Gary Myers

Forget dates and use timestamps. Using timestamp arithmetic, Oracle uses an INTERVAL datatype to avoid the rounding issue.

忘记日期并使用时间戳。使用时间戳算法，Oracle 使用 INTERVAL 数据类型来避免舍入问题。

select extract (minute from cast(later as timestamp) - cast(earlier as timestamp)) +
       (extract (hour from cast(later as timestamp) - cast(earlier as timestamp)) * 60)
from
(select TO_DATE ('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS') later,
       TO_DATE ('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS') earlier
from dual)
/

If the dates are more than a day apart, you'll need to add in consideration for that too.

如果日期相隔一天以上，您还需要考虑到这一点。

回答by Alex Martelli

Why not use ROUNDinstead of TRUNC? It gives you the result to the nearest day when applied to dates, nearest integer when applied to numbers (TRUNCtruncates, and seems to be running into strange arithmetic issues here).

为什么不使用ROUND代替TRUNC？当应用于日期时，它会为您提供最近一天的结果，应用于数字时最接近的整数（TRUNC截断，并且似乎在这里遇到了奇怪的算术问题）。

回答by John Y

Well, not that it should be any better, but have you tried FLOOR in place of TRUNC? (In the crazy event that it does work, you may want to test whether the result is negative, in which case you'll need to add one, because FLOOR "rounds" toward negative infinity while TRUNC rounds toward zero. Which may or may not be uglier to you than adding a fraction of a second before using TRUNC.)

好吧，并不是说它应该更好，但是您是否尝试过 FLOOR 代替 TRUNC？（在它确实有效的疯狂事件中，您可能想要测试结果是否为负，在这种情况下，您需要添加一个，因为 FLOOR 向负无穷大“舍入”，而 TRUNC 向零舍入。这可能或可能不会比在使用 TRUNC 之前添加几分之一秒更丑。）