Oracle Date Rounding

Question

This challenge is

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 - this is what you expect from the calculation.

This challenge 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) from dual

gives the result 94.

The proposed 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

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

Answer 1

Great question.

Date arithmetic is not entirely accurate due to data type conversions.

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

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

To solve this problem, you should be able to convert each date to an integer before performing arithmetic on it, for example:

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;

Answer 2

​​ . timestamp, 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)
/

, , .

Answer 3

ROUND TRUNC? , , , (TRUNC , , ).

Answer 4

Well, not that it was better, but did you try FLOOR instead of TRUNC? (In the crazy case when it works, you can check if the result is negative, in which case you need to add it, because FLOOR "goes around" to negative infinity, while TRUNC rounds to zero. Not uglier than add a fraction of a second before using TRUNC.)