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
扫一扫
1706
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
