[thelist] sql query for sum of timediff

[Matt Warden]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Canfield, Joel wrote:
>> what do you get with this:
>>
>> select firstname, lastname, timediff(endtime,starttime)
>> from presence
>> where starttime between '2006-05-31 0:00:00' and '2006-05-31 23:59:59'
> 
> list of names with time-formatted durations, as
> 
>     bob   user    0:03:16
>     jill  usette  0:11:32
> 
> etc. which is what I would expect.

Really? Because that's not what I was expecting.

So it would seem that sum() doesn't like summing those time values.

How about:

select firstname, lastname,
   SEC_TO_TIME( sum(TIME_TO_SEC(endtime)-TIME_TO_SEC(starttime)) )
from presence
where starttime between '2006-05-31 0:00:00' and '2006-05-31 23:59:59';

- --
Matt Warden
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEgEgwrI3LObhzHRMRAuWvAKDF9FD4S4I+JsmJ2SB6d02p5vDU7ACgg8LC
ubP3iUjsMCtEc0XLnnLqxys=
=Uajj
-----END PGP SIGNATURE-----