Home » SQL & PL/SQL » SQL & PL/SQL » Time difference and sum
Time difference and sum [message #398362] Thu, 16 April 2009 02:01 Go to next message
kuwait
Messages: 52
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
Re: Time difference and sum [message #398387 is a reply to message #398362] Thu, 16 April 2009 02:37 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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:
  


Current Time: Sat Dec 03 12:16:03 CST 2016

Total time taken to generate the page: 0.09035 seconds