Select difference between row dates in MySQL

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

For instance, 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 for what date functions I would use in MySQL, or is there a subselect that would do this?

(Of course, I don't mind putting WHERE date > '2011-01-01' to ig开发者_高级运维nore the first row.)

A correlated subquery could be of help:

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

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 imply that your id are continuous in your table, otherwise this won't work at all. And maybe MySQL will complain for the first row since t2.date will be null but I don't have the time to check now.