how to get number of days between consecutive records in table?

date, datediff, mysql, sql

Solution

You can do something along these lines:

SELECT T.id, 
       T.other_col, 
       T.yourdate, 
       IFNULL(datediff(T.yourdate,(select MAX(TT.yourdate) 
                                   FROM yourtable TT 
                                   WHERE TT.yourdate < T.yourdate 
                                   AND TT.other_col = 'abc')),0)
FROM yourtable T
where other_col = 'abc';

You can see it in this fiddle

This works by calculating the difference between the current record's date (`T.date`) and the date that is the biggest (`MAX(TT.date)`) of the one's that are smaller than the current record's date ( `WHERE TT.date < T.date` ).

When there is no smaller date, there's the COALESCE to make it be 0, instead of null.

Edit. added coalesce to give 0 in the first line

Problem

In MySQL, I have a table with several columns, one of them being a date column. I want to be able to make a query so i can get an additional column showing the number of days between the current date (the date for each row) and the date of the previous row. The order of records is by date, and I filter by other column. e.g. ``` id other_col date days_between_dates 1 abc 2013-05-01 0 2 abc 2013-05-07 6 3 abc 2013-05-09 2 4 abc 2013-05-19 10 5 abc 2013-05-25 6 6 abc 2013-06-03 9 7 abc 2013-06-14 11 ``` Obviously since the first row has nothing to compare to, it should show `0`. I know i can get the diff between two dates with the `datediff` MySQL function, e.g. ``` select datediff('2013-06-03','2013-05-25'); ``` My basic query is `select * from mytable where other_col = 'something' order by date;` but how can i make a query to directly obtain a result like in the example ? (I need the extra "`days_between_dates`" column). Also note that usually the id's are ordered, and without gaps, but i cannot guarantee that. only that i will sort by ascending date, and filtering by the "other_col" column.

Original source