MySQL subtract field with the field before

Question

I want to subtract two values, the current value, and value from the row before the current value at each value, basically creating a new table with only these new values, so that I can average them late.

Table structure looks like this:

-----------------------
id | keyTime   | millis| 
------------------------
1  | 22:00:00  | 10  | 
2  | 22:05:00  | 20  |
3  | 22:10:00  | 30  | 
4  | 22:15:00  | 40  |

And I would like to subtract like this:

---

id | dif    |
-------------
1  | 10-0   | 
2  | 20-10  |
3  | 30-20  | 
4  | 40-30  |

Answer 1

You can use a correlated subquery:

select t.*, (millis - coalesce(prev_millis, 0)) as diff
from (select t.*,
             (select t2.millis
              from t t2
              where t2.keyTime < t.keyTime
              order by t2.keyTime desc
              limit 1
             ) as prev_millis
      from t
     ) t;

This assumes that the id is unreliable. With the id values you have shown, you could simply use a left join.