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


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.



PHP Database Mailing List (
To unsubscribe, visit:

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to