Home » Developer & Programmer » Forms » Calculate hours / minutes
Calculate hours / minutes [message #407636] Wed, 10 June 2009 22:56 Go to next message
snsiddiqui
Messages: 168
Registered: December 2008
Senior Member
I want to calculate the total hours between two dates; my form takes input in this format hh24:mi.

Example:
DATE1 DATE2 TOTAL
22:10 07:00 = 09:50

Basically I want to display only the total hours and minutes in report.

[MERGED by LF]

[Updated on: Mon, 15 June 2009 01:36] by Moderator

Report message to a moderator

Re: Calculate Hours/Minutes [message #407639 is a reply to message #407636] Wed, 10 June 2009 23:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    data as (
  3      select trunc(sysdate)+22/24+10/1440 date1,
  4             trunc(sysdate)+1+7/24 date2
  5      from dual
  6    )
  7  select to_char(date1,'HH24:MI') date1,
  8         to_char(date2,'HH24:MI') date2,
  9         numtodsinterval((date2-date1)*86400,'SECOND') diff,
 10         substr(numtodsinterval((date2-date1)*86400,'SECOND'),12,5) diff2
 11  from data
 12  /
DATE1 DATE2 DIFF                           DIFF2
----- ----- ------------------------------ -----
22:10 07:00 +000000000 08:50:00.000000000  08:50

1 row selected.

Regards
Michel
Re: Calculate Hours/Minutes [message #407649 is a reply to message #407636] Wed, 10 June 2009 23:40 Go to previous messageGo to next message
snsiddiqui
Messages: 168
Registered: December 2008
Senior Member
Dear Thanks a lot for the good reply and you also increase my knowledge but there is still a problem, if I have values
like

DATE1 DATE2
07:00 06:30

or

DATE1 DATE2
07:00 07:00

then your solution is calculating 00:29 and 00:00 which is wrong so would you please try to solve this matter also.

Re: Calculate Hours/Minutes [message #407651 is a reply to message #407649] Wed, 10 June 2009 23:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    data as (
  3      select trunc(sysdate)+7/24 date1,
  4             trunc(sysdate)+1+6.5/24 date2
  5      from dual
  6    )
  7  select to_char(date1,'HH24:MI') date1,
  8         to_char(date2,'HH24:MI') date2,
  9         numtodsinterval((date2-date1)*86400,'SECOND') diff,
 10         substr(numtodsinterval((date2-date1)*86400,'SECOND'),12,5) diff2
 11  from data
 12  /
DATE1 DATE2 DIFF                           DIFF2
----- ----- ------------------------------ -----
07:00 06:30 +000000000 23:30:00.000000000  23:30

1 row selected.

SQL> with 
  2    data as (
  3      select trunc(sysdate)+7/24 date1,
  4             trunc(sysdate)+7/24 date2
  5      from dual
  6    )
  7  select to_char(date1,'HH24:MI') date1,
  8         to_char(date2,'HH24:MI') date2,
  9         numtodsinterval((date2-date1)*86400,'SECOND') diff,
 10         substr(numtodsinterval((date2-date1)*86400,'SECOND'),12,5) diff2
 11  from data
 12  /
DATE1 DATE2 DIFF                           DIFF2
----- ----- ------------------------------ -----
07:00 07:00 +000000000 00:00:00.000000000  00:00

1 row selected.

So it is not wrong.

A time refers to a date, your examples do not show which dates you are refering, I assume date2 is always equal or after date1 and with a difference less than 1 day.
If this is not the case, you have to precise your requirements.

Regards
Michel
TOTAL HOURS Calculation [message #408169 is a reply to message #407636] Mon, 15 June 2009 00:28 Go to previous messageGo to next message
snsiddiqui
Messages: 168
Registered: December 2008
Senior Member
I want to calculate the total machine production time like
I have different machines in production and I am calculating there production time.


MACHINE START TIME END TIME DURATION
PRINTING1 07:30 09:30 02:00
PRINTING2 07:15 10:30 03:15
PRINTING3 09:45 12:50 03:05
------
08:20

Now I want to calculate TOTAL DURATION HOURS.
Re: Calculate Hours/Minutes [message #408173 is a reply to message #407651] Mon, 15 June 2009 00:40 Go to previous messageGo to next message
snsiddiqui
Messages: 168
Registered: December 2008
Senior Member
After calculating the duration of two different times now I want to calculate there TOTAL or want to see the sum of time.
Re: Calculate Hours/Minutes [message #409478 is a reply to message #408173] Mon, 22 June 2009 19:57 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
See http://www.orafaq.com/forum/m/409477/67467/#msg_409477

Please don't cross post.

David
Previous Topic: TOTAL HOURS Calculation
Next Topic: Forms 6i - disabled two buttons in vertical toolbar
Goto Forum:
  


Current Time: Sun Dec 04 16:34:58 CST 2016

Total time taken to generate the page: 0.09643 seconds