Possible to GROUP BY across different columns in mariadb/mysql

Question

I have a table full of events similar to this:

`date`     | start_time | end_time | length 
--------------------------------------------
2022-01-10 | 07:00:00   | 16:00:00 |      9
2022-01-11 | 07:00:00   | 16:00:00 |      9
2022-01-11 | 16:00:00   | 20:00:00 |      5
2022-01-12 | 07:00:00   | 12:00:00 |      5
2022-01-12 | 16:00:00   | 20:00:00 |      5

I want to group by contiguous events on each date. See how on 2022-01-11 there are two entries, one ending at the same time the next one starts? (but not on 12th)

I'd like to get something like this:

select `date`, SUM(length) from events
group by `date`, ????
date     | length
2022-01-10 |      9
2022-01-11 |     14
2022-01-12 |      5
2022-01-12 |      5

Is this even possible?

Answer 1

If you are using MySQL 8 or MariaDB 10.2, you can use LEAD() or LAG() to see the next or previous row (given that you have an ORDER BY), then you can do the test to see if they are contiguous. (This is not a "grouping" function.)