Get the previous date in Mysql

Question

I have a table formatted similar to this:

Date | ID | Value | Difference

I need to get the difference between a record's value column, and the previous record's value column based off of the date.

I.E

2 days ago | cow | 1 | Null

Yesterday | cow | 2 | Null

Today | cow | 3 | Null

Yesterdays difference would be 1, and today's difference would be 1.

basically, I need to get the previous record based off the date, I don't know the interval's between each record. I've been stumped on this for a while. I am using Mysql, and Python to do the majority of the calculations.

Answer 1

Use a SELECT... WHERE date <= NOW() && date >= ( NOW() - 90000 ) (90,000 is 25 hours, giving you a little leeway with the insert time), and then take the difference between the rows in python.

Answer 2

In Python, or stored procedure, or other language :

Query Data using your criteria
Fetch first record
Begin Loop
     Get value of previous record by executing another query (select value from table where   date<current_record_date order by date desc limit 1)
     Update current_record set diff=value-previous value
     Fetch next record, exit loop if no more
Iterate Loop