OraFAQ Forum: SQL & PL/SQL » Time difference and sum

Home » SQL & PL/SQL » SQL & PL/SQL » Time difference and sum

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

Time difference and sum [message #398362]

Thu, 16 April 2009 02:01

kuwait
Messages: 55
Registered: 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

Report message to a moderator

Re: Time difference and sum [message #398387 is a reply to message #398362]

Thu, 16 April 2009 02:37

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account 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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 18:58:47 CST 2025