Oracle Date Rounding

Question

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

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

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

produces a result of 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

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.

Answer 1

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

Answer 2

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

Answer 3

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.

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;

Answer 4

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

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.