Geting values by timestamp to find percent change. Mysql

I want to find the precent change in two values from a mysql database.

I have a cron job set up to insert values approximately every hour..

Each value has a unix timestamp associated with it and I want to get the previous days(~24hrs) value and compare with the current one开发者_如何学Go to get percent increase or decrease.

Table looks like this:

+-----+----+
|value|time|
+-----+----+

Try this assuming that there are exact matches from the previous day:

SELECT T1.time, (T1.value - T2.value) * 100 / T2.value AS percent_change
FROM yourtable T1
JOIN yourtable T2
ON T1.time = T2.time + (60 * 60 * 24) --# seconds in a day
WHERE T1.time > ....

You'd need to sum the values from day 1 and day 2, yes? This would give you the change between readings by calendar day.

SELECT t1.time, (sum(t1.value) - sum(t2.value))
from table t1, table t2
where t1.time>t2.time
and t1.time = t2.time + (60 * 60 * 24)
group by date(from_unixtime(t1.time)