Mysql Find free time (slots) between appointments for booking system

Question

I'm trying to find the free time slots between 2 datetime field that match a specific duration for a booking system, but I'am stuck on the correct query.

my db Events:

+----------+---------------------+---------------------+---------+
| Event_ID | StartTime           | EndTime             | Pers_ID |
+----------+---------------------+---------------------+---------+
|        3 | 2013-09-21 16:00:00 | 2013-09-21 16:30:00 |       6 | 
|       21 | 2013-09-21 09:00:00 | 2013-09-21 09:15:00 |       6 | 
|        5 | 2013-09-21 09:15:00 | 2013-09-21 10:15:00 |       6 | 
|       64 | 2013-09-21 15:00:00 | 2013-09-21 15:45:00 |       6 | 
|       32 | 2013-09-21 10:15:00 | 2013-09-21 11:30:00 |       6 | 
|        6 | 2013-09-21 13:00:00 | 2013-09-21 14:45:00 |       6 | 
+----------+---------------------+---------------------+---------+

The result must be the free possible slots of 75 minutes with Pers_ID=6 between 2013-09-21 09:00:00 AND 2013-09-21 22:00:00 , and also they have to start right after an EndTime already in the database

Already thanks for the help,
E-raser

Answer 1

SELECT AvailStartTime, AvailEndTime
FROM (
    SELECT @lastEndTime as AvailStartTime, StartTime as AvailEndTime, @lastEndTime := EndTime
    FROM (SELECT StartTime, EndTime
          FROM Events
          WHERE Pers_ID = 6
            AND EndTime >= '2013-09-21 09:00'
            AND StartTime < '2013-09-21 22:00'
          ORDER BY StartTime) e
    JOIN (SELECT @lastEndTime := NULL) init) x
WHERE AvailEndTime > DATE_ADD(AvailStartTime, INTERVAL 75 MINUTE)

DEMO