Forum Stats

  • 3,770,491 Users
  • 2,253,125 Discussions
  • 7,875,485 Comments

Discussions

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

MD DIDARUL ISLAM
MD DIDARUL ISLAM 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:

Best Answers

  • mathguy
    mathguy Member Posts: 10,167 Blue Diamond
    Accepted Answer

    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.

    MD DIDARUL ISLAM
  • User_H3J7U
    User_H3J7U Member Posts: 693 Silver Trophy
    Accepted Answer

Answers

  • mathguy
    mathguy Member Posts: 10,167 Blue Diamond
    Accepted Answer

    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.

    MD DIDARUL ISLAM
  • User_H3J7U
    User_H3J7U Member Posts: 693 Silver Trophy
    Accepted Answer
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    HI, @MD DIDARUL ISLAM

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

    MD DIDARUL ISLAM