Interesting challenge. I tend to write TSQL more these days so this may not work on MySQL but the subquery approach should give you an idea of the method. I just typed this up so it may not even work! :-)
select min(stopped) as Stopped,Started, (Started-min(stopped)) as Duration from (select top 100 percent tbl1.myTime as Stopped,min(tbl2.myTime) as Started from (select * from myTable where mySpeed = 0) as tbl1 left join (select top 100 percent * from myTable where mySpeed <> 0 order by myTime ) as tbl2 on tbl2.myTime > tbl1.myTime group by tbl1.myTime order by tbl1.myTime ) as tbl3 where started is not NULL group by started <>< Ryan -----Original Message----- From: Jeff Hibser [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 24, 2002 1:09 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Re: WELCOME to [EMAIL PROTECTED] Hello, I am relatively new to php, and have a question regarding polling a mysql database and defining groups of data: Say I have a table that looks like this: Time | Speed -------------------- 1016278580 | 0 1016278582 | 0 1016278584 | 0 1016278586 | 3 1016278588 | 7 1016278590 | 5 1016278592 | 4 1016278594 | 0 1016278596 | 0 1016278598 | 2 1016278600 | 0 Now, the time is epoch time. Speed is MPH (so 0 means I am stopped). What I want to do is make a call into mysql that will return to me when I stopped, and for how long. IE: You stop at 1016278580 for 6 seconds (calculated until speed !=0). You also stopped at 1016278594 for 4 seconds You also stopped at 1016278600 , and are still stopped (or something like that) Whats the best way to do this? I can query the database for every row where speed = 0, but how do I make the distinction between the groups of 0's (IE, in the example above I would essentially have three "groups" of zeros that I would need to identify). Is there someway to pull the data out of mysql in this format (Select * WHERE speed=0 UNTIL speed !=0)? Something like that? Or would this be better handled by PHP after pulling the data out of mysql? Any help I can get on this would be greatly appreciated. Thanks. -Jeff -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
