How to find number of records since a certain change?

[yungbloodreborn]

Here's what I'm trying to do. I have a database tracking the value of something, and I have 1 record per day. Each record in the table holds the current value, and the average of the last 30 days, and of course, a time stamp.

 

What I want to know is how long has the current trend been over/under the average.

 

Is there an easy way to ask this of mysql? Or would I need to add another column, and have a program manually walk through all the data to fill that column in?

[yungbloodreborn]

Well, I figured it out myself...

 

select ((select ts from item where price>avg30-1 order by ts desc limit 1)-(select ts from item where price<avg30+1 order by ts desc limit 1))/(24*60*60);

 

If the result is positive, it is the number of days it has been over avg30.

If the result is negative, it is the number of days it has been under avg30 (* -1)

If the result is 0, the price is equal to avg30...

 

I hope this helps someone... Btw, ts is a unix timestamp...

-YB