## Forum Stats

• 3,770,491 Users
• 2,253,125 Discussions

Discussions

# Plugin Clock Calculation find out Duration in Munities. Exact result can not given.why?

Member Posts: 3 Green Ribbon

begin

select ((nvl(trunc(24*mod(end_time - start_time,1)),0))*60)+ (nvl(trunc( mod(mod(end_time - start_time,1)*24,1)*60 ),0)) into :P7_DURATION

from (select TO_DATE( :P7_START_TIME, 'HH24:MI') start_time , to_date(:P7_END_TIME, 'HH24:MI') end_time

from dual);

exception

when no_data_found then

null;

end;

Tagged:

• Member Posts: 10,167 Blue Diamond

If I'm reading your code correctly, you are looking at trunc(start_time, 'mi') and trunc(end_time, 'mi') and you don't understand why your "minutes difference" calculation doesn't equal the difference of those two truncated values.

You compute the difference, essentially, as trunc(end_time - start_time) (truncated to minutes).

And you expect that to be equal to the difference of trunc(end_time) and trunc(start_time).

That expectation is 100% wrong - it's elementary school arithmetic, really.

Your actual start time and end time may be, say, 12:00:45 and 12:30:05. If you truncate the times you get 12:00 and 12:30. But the difference between the times is 29 minutes and 20 seconds; if you truncate that to minutes, the result is 29 minutes. There is nothing wrong with that; and the correct answer is indeed 29 minutes, it should not be 30.

If you think that your users (the report readers) will be confused by this, like you were, you have at least two choices. One is to show the start time and end time in full, without truncating them to minutes. The other is to include a footnote to explain briefly that rounding makes values not to exactly match each other.

Most importantly, don't change the calculation to produce a different result. The calculation (as far as I can tell) produces the correct answer. Leave it alone.

• Member Posts: 693 Silver Trophy

Нou don't need to use trunc(mod())+mod(mod()) to get minutes. Subtracting dates gives the number of days.

• Member Posts: 10,167 Blue Diamond

If I'm reading your code correctly, you are looking at trunc(start_time, 'mi') and trunc(end_time, 'mi') and you don't understand why your "minutes difference" calculation doesn't equal the difference of those two truncated values.

You compute the difference, essentially, as trunc(end_time - start_time) (truncated to minutes).

And you expect that to be equal to the difference of trunc(end_time) and trunc(start_time).

That expectation is 100% wrong - it's elementary school arithmetic, really.

Your actual start time and end time may be, say, 12:00:45 and 12:30:05. If you truncate the times you get 12:00 and 12:30. But the difference between the times is 29 minutes and 20 seconds; if you truncate that to minutes, the result is 29 minutes. There is nothing wrong with that; and the correct answer is indeed 29 minutes, it should not be 30.

If you think that your users (the report readers) will be confused by this, like you were, you have at least two choices. One is to show the start time and end time in full, without truncating them to minutes. The other is to include a footnote to explain briefly that rounding makes values not to exactly match each other.

Most importantly, don't change the calculation to produce a different result. The calculation (as far as I can tell) produces the correct answer. Leave it alone.

• Member Posts: 693 Silver Trophy