我有一张桌子,上面有连续的时间和议程日期。
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:00sqlfiddle/#!2/54d9f6
正如您在05-02-15所看到的,从19:00到19:40的时间是连续的
As you can see on 05-02-15 the time from 19:00 until 19:40 is consecutive
如您在06-02- 15从19:00到19:15的时间是连续的
As you can see on 06-02-15 the time from 19:00 until 19:15 is consecutive
如您在06-02-15所看到的,从19:25到19:40的时间是连续的
As you can see on 06-02-15 the time from 19:25 until 19:40 is consecutive
开始时间和结束时间总是相差5分钟。
The begin time and end time always have a 5 minute difference.
我想获取所有日期,这些日期的连续时间间隔为 x 分钟。因此,当 x = 30 时,结果为:
05-02-15当 x = 10 ,结果是:
05-02-15 06-02-15方法的想法
也许第一步是获取所有连续的部分,其次计算一个部分中的记录数(当x = 30时,我们至少需要30分钟。 / 5分钟。 = 6)。
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).
推荐答案此查询检查您是否有 @ x / 5 在接下来的 @x 分钟内提供免费广告位。如果是这样,则它们覆盖整个 @x 分钟的时间间隔,意味着它们是连续的。
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<addtime(t1.begin,sec_to_time(@x*60)) group by t1.id having count(*)=@x/5sqlfiddle/#!2/54d9f6/50
更多推荐
MySQL:连续时间内可用的时间跨度
发布评论