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