Discussions
Categories
 382.3K All Categories
 2.1K Data
 209 Big Data Appliance
 1.9K Data Science
 448K Databases
 221K General Database Discussions
 3.7K Java and JavaScript in the Database
 25 Multilingual Engine
 520 MySQL Community Space
 467 NoSQL Database
 7.8K Oracle Database Express Edition (XE)
 2.9K ORDS, SODA & JSON in the Database
 492 SQLcl
 3.9K SQL Developer Data Modeler
 186.2K SQL & PL/SQL
 21K SQL Developer
 293.3K Development
 7 Developer Projects
 128 Programming Languages
 290K Development Tools
 95 DevOps
 3K QA/Testing
 645.5K Java
 24 Java Learning Subscription
 36.9K Database Connectivity
 150 Java Community Process
 104 Java 25
 22.1K Java APIs
 137.9K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 16 Java Essentials
 144 Java 8 Questions
 85.9K Java Programming
 79 Java Puzzle Ball
 65.1K New To Java
 1.7K Training / Learning / Certification
 13.8K Java HotSpot Virtual Machine
 94.2K Java SE
 13.8K Java Security
 198 Java User Groups
 24 JavaScript  Nashorn
 Programs
 267 LiveLabs
 36 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.8K Other Languages
 2.3K Chinese
 166 Deutsche Oracle Community
 1.2K Español
 1.9K Japanese
 230 Portuguese
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;
Best Answers

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

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