## 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?

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:

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

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

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

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

• Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of how you get those results from that data. In this case, create a table that has a few (maybe 5) test values for p7_start_time and p7_end_time, and show the values of p7_duaration you want to compute from each.

Always say which version of Oracle you're using (e.g. 12.2.0.1.0).