I have the following table:

And I need to sort the dates decending, group by ID and Type and get the time between the dates by ID and Type, either in seconds or months days

CodePudding user response：

We can use DATEDIFF() here along with the LAG() analytic function:

SELECT id, date, type,
       DATEDIFF(date, LAG(date, 1, date) OVER (PARTITION BY id ORDER BY date)) AS diff
FROM yourTable
ORDER BY id, date;

CodePudding user response：

If you are using MySQL 8.0,window functions are strongly recommended. Otherwise,you might have to go a long way. Here is the query written and tested in workbench using 5.7 :

select tb1.id,tb1.date,tb1.type,ifnull(datediff(tb1.date,tb2.date),0) as 'time diff'
from
    (select id,date,type, @row_id:=@row_id 1 as row_id
    from
        (select id,date,type
        from test
        group by id,date,type
        order by id,date)t1,
        (select @row_id:=0) t
    ) tb1
left join
    (select id,date,type, @row_num:=@row_num 1 as row_num
    from
        (select id,date,type
        from test
        group by id,date,type
        order by id,date)t2,
        (select @row_num:=0) t
    ) tb2
on tb1.id=tb2.id and tb1.row_id-tb2.row_num=1
order by tb1.id,tb1.date
;