OraFAQ Forum: Forms » Calculate hours / minutes

Home » Developer & Programmer » Forms » Calculate hours / minutes

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Calculate hours / minutes [message #407636]

Wed, 10 June 2009 22:56

snsiddiqui
Messages: 172
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

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: Calculate Hours/Minutes [message #407649 is a reply to message #407636]

Wed, 10 June 2009 23:40

snsiddiqui
Messages: 172
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.

Report message to a moderator

Re: Calculate Hours/Minutes [message #407651 is a reply to message #407649]

Wed, 10 June 2009 23:49

Michel Cadot
Messages: 68686
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

Report message to a moderator

TOTAL HOURS Calculation [message #408169 is a reply to message #407636]

Mon, 15 June 2009 00:28

snsiddiqui
Messages: 172
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.

Report message to a moderator

Re: Calculate Hours/Minutes [message #408173 is a reply to message #407651]

Mon, 15 June 2009 00:40

snsiddiqui
Messages: 172
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.

Report message to a moderator

Re: Calculate Hours/Minutes [message #409478 is a reply to message #408173]

Mon, 22 June 2009 19:57

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

See http://www.orafaq.com/forum/m/409477/67467/#msg_409477

Please don't cross post.

David

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 10:39:55 CDT 2024