Home » Developer & Programmer » Forms » Calculate hours / minutes
Calculate hours / minutes Wed, 10 June 2009 22:56
 snsiddiqui Messages: 168Registered: 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: 65086Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 snsiddiqui Messages: 168Registered: 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
 Michel Cadot Messages: 65086Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 snsiddiqui Messages: 168Registered: 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
 snsiddiqui Messages: 168Registered: 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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount Moderator
See http://www.orafaq.com/forum/m/409477/67467/#msg_409477