MySQL: a timespan available within consecutive tim

问题:

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

http://sqlfiddle.com/#!2/54d9f6

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

回答1:

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/5

http://sqlfiddle.com/#!2/54d9f6/50

回答2:

User variables are very useful in mysql

SELECT date,MIN(CASE WHEN BEGIN!='00:00:00' THEN BEGIN END) m,
            MAX(CASE WHEN END!='00:00:00' THEN END END) mm
FROM
  ( SELECT BEGIN,END,date, CASE
                               WHEN
                           END = BEGIN +INTERVAL 5 MINUTE THEN @n ELSE @n:=@n+1 END AS g
   FROM agenda_specialists,
     (SELECT @n:=0) x
   ORDER BY id) s
GROUP BY date, g
HAVING ((TIME_TO_SEC(mm) - TIME_TO_SEC(m))/60)>=40

Esentially you want to check if the begin is equal with the end+5 minutes if not you increment a variable you can GROUP BY,it creates the intervals besides the different date.The rest is easy.I had to change the collations,for some reason it gave me illegal mix of collation.Just play with the number at the end.

FIDDLE

Edit:

Run

SHOW VARIABLES LIKE '%char%';

You probably have character_set_server='latin1'

Go into your config file,my.cnf and add or uncomment these lines:

character-set-server = utf8
collation-server = utf8_general_ci

Tested on my machine 5.5