Home » SQL & PL/SQL » SQL & PL/SQL » Time difference and sum
Time difference and sum Thu, 16 April 2009 02:01
 kuwait Messages: 52Registered: October 2007 Member
Hi if i have two date data type I want to find the time difference also I want to find the sum of the tim differences for example:

tfrom date;
tto date;

10:45 am
8:30 am -
--------
2:15

11:00 am
9:10 am
------
1:50

2:15
1:50 +
-----
4:05

How to do that?

I managed to find the difference

```(
decode(to_char(tto,'MI'),0,

(
decode(to_char(tfrom,'MI'),0,
(
decode(to_char(tto,'HH'),1 , (13- to_char(tfrom,'HH'))||':00',
2,(14- to_char(tfrom,'HH'))||':00',
(to_char(tto,'HH') - to_char(tfrom,'HH'))||':00'
)
)

,

(
decode(to_char(tto,'HH'),1 , (12- to_char(tfrom,'HH'))
||':'||(60-to_char(tfrom,'MI')),
2,(13- to_char(tfrom,'HH')) ||':'||(60-to_char(tfrom,'MI')),
(to_char(tto,'HH') - to_char(tfrom,'HH') -1)
||':'||(60-to_char(tfrom,'MI'))
)
)

)
)
,

(
decode(to_char(tfrom,'MI')/to_char(tto,'MI'),  0,
(
decode(to_char(tto,'HH'),1 , (13- to_char(tfrom,'HH'))
||':'||((to_char(tto,'MI')-to_char(tfrom,'MI'))),
2,(14- to_char(tfrom,'HH'))
||':'||((to_char(tto,'MI')-to_char(tfrom,'MI'))),

(to_char(tto,'HH') - to_char(tfrom,'HH'))
||':'||((to_char(tto,'MI')-to_char(tfrom,'MI')))
)
)

,

(
decode(to_char(tto,'HH'),1 , (12- to_char(tfrom,'HH'))
||':'||(60-(to_char(tfrom,'MI')-to_char(tto,'MI'))),
2,(13- to_char(tfrom,'HH'))
||':'||(60-(to_char(tfrom,'MI')-to_char(tto,'MI'))),
(to_char(tto,'HH') - to_char(tfrom,'HH')-1)
||':'||(60-(to_char(tfrom,'MI')-to_char(tto,'MI')))
)
)

)
)
)
)
thrs
```

Is there an easier way?

Thanks
Re: Time difference and sum [message #398387 is a reply to message #398362] Thu, 16 April 2009 02:37
 Michel Cadot Messages: 65084Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
If the sum is a sum of columns in same row, use INTERVAL datatype.
If the sum is a sum of different rows, Convert to seconds, sum the seconds and convert back to your format.
You can also create your own aggregate function to sum INTERVAL datatype or even your datatype.

Regards
Michel

[Updated on: Thu, 16 April 2009 02:38]

Report message to a moderator

 Previous Topic: SQL Question / Analytic function Next Topic: how to handle this - how to escape &
Goto Forum:

Current Time: Sun Jul 23 06:03:16 CDT 2017

Total time taken to generate the page: 2.11066 seconds