Difference in dates grouped by users and product mysql

user_7459309 0
Viewed 26

I have the following table:

id date type
001 2022-01-01 A
001 2022-01-03 B
001 2022-01-02 B
001 2022-02-02 A
002 2022-01-01 A
002 2022-01-03 B
004 2022-01-01 A
004 2022-01-03 B

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

id date type time diff
001 2022-01-01 A 0
001 2022-01-02 B 1
001 2022-01-03 B 1
001 2022-02-02 A 31
002 2022-01-01 A 0
002 2022-01-03 B 2
004 2022-01-01 A 0
004 2022-01-03 B 2
jupyter-notebook mysql
2 Answers

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;
user_18632290

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
;
user_185543690
Related