Dealing With MySQL's new Time Calculations

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I have an interesting problem I'm trying to solve.  Right now, I'm keeping
track of sessions starting 
and ending times with a PHP datetime field, and calculating the total time
spent in all sessions with 
the new PHP time command... something like this:

$total_minutes = mysql_query("select
((sum(time_to_sec(timediff(tourney.finish, tourney.start))))/
(60*60)) FROM tourney WHERE tourney.closed = '1' AND tourney.userid =
'$userid'");

but the problem is that in some cases, there are overlapping "sessions" or
tourneys.  That is, 
someone might have a tourney.start at 10:00 with a tourney.finish at 10:45
and start another 
tourney.start at 10:15 and finish at 10:55.  right now, i'd get the sum of
both sessions, 45 minutes 
+ 40 minutes = 85 minutes, where I'd rather have the calculation of 10:00
(the earliest start) to 10:
55 (the latest finish), for a total of 55 minutes.

I can't get my head around the right logic to use to get to an answer like
that.  There may be 
hundreds of registered tourneys, so testing one against the rest to see if
there is a matching time 
seems very wasteful and sloppy.

Any suggestions on how I might proceed?  Perhaps a one time query when I
add the record into the 
database (that would make changing the record difficult) to compare the
session time to other 
session times somehow?

Thanks in advance

Jake

--------------------------------------------------------------------
mail2web - Check your email from the web at
http://mail2web.com/ .

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux