Skip to Main Content
  • Questions

  • Minute difference up to one minute from dates

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Partha.

Asked: January 11, 2002 - 12:21 pm UTC

Last updated: June 18, 2004 - 10:59 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Tom,

I have follwing dates and i want's to display difference in minutes,
when i use follwing query , i'm not getting why?

E_COMPLETION_TIME = '12/04/2001 11:32:00'

E_BEGIN_TIME = '12/04/2001 11:31:00'

select TRUNC(MOD(("LMF"."EVENT_DETAILS"."E_COMPLETION_TIME" - "LMF"."EVENT_DETAILS"."E_BEGIN_TIME")*1440,60)) Minutes,
from event_details;

Actually i should get one minute difference in the result, but i'm getting 0 why?

Can you please give me the explaination.

Thanks in advance.


and Tom said...

It is small errors introduced in the mixed mode arithmetic. It sees "ints", "reals", large scale, small scale numbers and rounding errors creep in.


If you use 24*60 (which I personally find more readable), the math works. If you through in a little extra math to force the constant to a real, it works:


ops$tkyte@ORA815.US.ORACLE.COM> select x, y from t;

X Y
------------------- -------------------
12/04/2001 11:32:00 12/04/2001 11:31:00

ops$tkyte@ORA815.US.ORACLE.COM> select
2 to_char( (x-y)*1440, '9.99999999999999999999999999999999999999999' )
3 from t;

TO_CHAR((X-Y)*1440,'9.9999999999999999999999
--------------------------------------------
.99999999999999999999999999999999999999940

ops$tkyte@ORA815.US.ORACLE.COM> select
2 to_char( round((x-y)*1440,5), '9.99999999999999999999999999999999999999999' )
3 from t;

TO_CHAR(ROUND((X-Y)*1440,5),'9.9999999999999
--------------------------------------------
1.00000000000000000000000000000000000000000

ops$tkyte@ORA815.US.ORACLE.COM> select
2 to_char( (x-y)*24*60, '9.99999999999999999999999999999999999999999' )
3 from t;

TO_CHAR((X-Y)*24*60,'9.999999999999999999999
--------------------------------------------
1.00000000000000000000000000000000000000000

ops$tkyte@ORA815.US.ORACLE.COM> select
2 to_char( (x-y)*2*1440/2, '9.99999999999999999999999999999999999999999' )
3 from t;

TO_CHAR((X-Y)*2*1440/2,'9.999999999999999999
--------------------------------------------
1.00000000000000000000000000000000000000000



See the (x-y)*1440 is just a little smaller than one (you normally won't see that)


Consider:

ops$tkyte@ORA815.US.ORACLE.COM> select TRUNC(MOD((x - y)*1440,60)) from t;

TRUNC(MOD((X-Y)*1440,60))
-------------------------
.000000000

ops$tkyte@ORA815.US.ORACLE.COM> select MOD((x - y)*2*1440/2,60) from t;

MOD((X-Y)*2*1440/2,60)
----------------------
1.000000000

ops$tkyte@ORA815.US.ORACLE.COM> select MOD(round((x - y)*1440,5),60) from t;

MOD(ROUND((X-Y)*1440,5),60)
---------------------------
1.000000000

ops$tkyte@ORA815.US.ORACLE.COM> select MOD((x - y)*24*60,60) from t;

MOD((X-Y)*24*60,60)
-------------------
1.000000000


rounding to 5 decimal places (biggest number of decimals in a day with seconds), can correct this as well.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Minute Difference up to one

A reader, January 11, 2002 - 1:59 pm UTC


how to Round the time

Syed, June 18, 2004 - 2:20 am UTC

Hi Tom,

The answer was very useful. But here if we want to round the date, how to do that?
eg: all target db date field is timestamp with local time zone.

I need to round the date '02/feb/04 05:20:00000 AM' to '02/feb/04' and retrieve the data coresponds to the date value '02/feb/04'

Please help me to do this.

Thanks in advance.

Tom Kyte
June 18, 2004 - 10:38 am UTC

use round()? it is a funtion, it works on dates.

Round ?

Andrew Gilfrin, June 18, 2004 - 10:59 am UTC 

is round() really what you want to do with the date ? 
or do you want to do truncate the date using trunc()

SQL> select to_char(sysdate,'DD/MON/RRRR HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD/
--------------------
18/JUN/2004 15:41:27

SQL>  select round(sysdate) from dual;

ROUND(SYS
---------
19-JUN-04

SQL>  select trunc(sysdate) from dual;

TRUNC(SYS
---------
18-JUN-04

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.