0

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?

1
  • 1
    Search for "gaps and islands".
    – mustaccio
    Commented Jan 7, 2022 at 1:26

1 Answer 1

0

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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.