yungbloodreborn Posted November 28, 2010 Share Posted November 28, 2010 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? Quote Link to comment https://forums.phpfreaks.com/topic/220038-how-to-find-number-of-records-since-a-certain-change/ Share on other sites More sharing options...
yungbloodreborn Posted November 29, 2010 Author Share Posted November 29, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/220038-how-to-find-number-of-records-since-a-certain-change/#findComment-1140761 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.