mysql获取连续时间间隔_MySQL：连续时间内可用的时间跨度

I have a table with consecutive times and dates for an agenda.

ID date begin_time end_time

1 05-02-15 19:00:00 19:05:00

2 05-02-15 19:05:00 19:10:00

3 05-02-15 19:10:00 19:15:00

4 05-02-15 19:15:00 19:20:00

5 05-02-15 19:20:00 19:25:00

6 05-02-15 19:25:00 19:30:00

7 05-02-15 19:30:00 19:35:00

8 05-02-15 19:35:00 19:40:00

9 06-02-15 19:00:00 19:05:00

10 06-02-15 19:05:00 19:10:00

11 06-02-15 19:10:00 19:15:00

12

13

14 06-02-15 19:25:00 19:30:00

15 06-02-15 19:30:00 19:35:00

16 06-02-15 19:35:00 19:40:00

As you can see on 05-02-15 the time from 19:00 until 19:40 is consecutive

As you can see on 06-02-15 the time from 19:00 until 19:15 is consecutive

As you can see on 06-02-15 the time from 19:25 until 19:40 is consecutive

The begin time and end time always have a 5 minute difference.

I want to have all the dates, that have a consecutive timespan of x minutes. So when x = 30, the result is:

05-02-15

when x = 10, the result is:

05-02-15

06-02-15

Idea for an approach

Maybe the first step is to get all the consecutive parts, secondly count the number of records in a part (when x = 30, we need at least 30 min. / 5 min. = 6).

解决方案

This query check if you have @x/5 free slots in next @x minutes. And if so, than them cover whole @x minutes interval, means them are consecutive.

set @x=15;

select distinct t1.date

from

`agenda_specialists` as t1 join

`agenda_specialists` as t2 on

t2.date=t1.date and

t2.begin>=t1.begin and

t2.begin

group by t1.id

having count(*)=@x/5