Choose the difference between row dates in MySQL

Question

I want to calculate the difference in unique date fields between different rows in the same table.

For example, given the following data:

id | date ---+------------ 1 | 2011-01-01 2 | 2011-01-02 3 | 2011-01-15 4 | 2011-01-20 5 | 2011-01-10 6 | 2011-01-30 7 | 2011-01-03 

I would like to generate a query that produces the following:

 id | date | days_since_last ---+------------+----------------- 1 | 2011-01-01 | 2 | 2011-01-02 | 1 7 | 2011-01-03 | 1 5 | 2011-01-10 | 7 3 | 2011-01-15 | 5 4 | 2011-01-20 | 5 6 | 2011-01-30 | 10 

Any suggestions on what date functions I will use in MySQL, or is there a subheading that will do this?

(Of course, I don't mind putting WHERE date > '2011-01-01' ignore the first line.)

Answer 1

A correlated subquery can help:

 SELECT id, date, DATEDIFF( (SELECT MAX(date) FROM atable WHERE date < t.date), date ) AS days_since_last FROM atable AS t 

Answer 2

Something like this should work:

  SELECT mytable.id, mytable.date, DATEDIFF(mytable.date, t2.date) FROM mytable LEFT JOIN mytable AS t2 ON t2.id = table.id - 1 

However, this means that your id is continuous in your table, otherwise it will not work at all. And maybe MySQL will complain about the first row, since t2.date will be null , but I don't have time to check.