MySQL - Find available time slots

Question

I'm working on a booking system, where it should be possible for users to book services with different service duration.

I already have a MySQL table which stores bookings, table with available time slots and query which return free time slots.

My bookings table looks like:

point_id     start (datetime)       end (datetime)
1            2017-06-17 14:50:00    2017-06-17 14:59:59
1            2017-06-17 15:10:00    2017-06-17 15:19:59
1            2017-06-17 15:40:00    2017-06-17 15:44:59
2            2017-06-17 15:50:00    2017-06-17 16:04:59
2            2017-06-17 16:05:00    2017-06-17 16:14:59
...

datetimes table contains 10 minutes intervals:

datetime (datetime)
2017-06-17 14:40:00
2017-06-17 14:50:00
2017-06-17 15:00:00
2017-06-17 15:10:00
2017-06-17 15:20:00
2017-06-17 15:30:00
2017-06-17 15:40:00
...

Here is the query that return free time slots:

SELECT
    a.datetime AS blockstart,
    DATE_ADD(a.datetime, INTERVAL 599 SECOND) AS blockend
FROM
    datetimes a
LEFT JOIN
    (
        SELECT *
        FROM bookings
        WHERE point_id = 1
    ) b ON
        (a.datetime BETWEEN b.start AND b.end)
        OR
        (DATE_ADD(a.datetime, INTERVAL 599 SECOND) BETWEEN b.start AND b.end)
WHERE b.id IS NULL AND a.datetime BETWEEN '2017-06-17 00:00:00' AND '2017-06-17 23:59:59';

If the service duration is less than 25 minutes - everything is ok. The problem is with services with duration longer than 25 minutes - my query returns slots that overlaps already booked 10 minutes services. For example, i have booking from 13:00:00 to 13:09:59, if I query interval 1499 seconds it returns 12:50:00 which will overlap next booking. Can anyone help me to solve this problem with overlapping servics?

Answer 1

You can find out the not-available timeslots, then join back with datetimes table to find out the available slots:

SELECT distinct all_slots.`datetime`
FROM datetimes AS all_slots
LEFT JOIN (
  SELECT `datetime`
  FROM datetimes AS d
  JOIN bookings AS b
    ON b.start BETWEEN d.`datetime` AND DATE_ADD(d.`datetime`, INTERVAL 599 SECOND)
    OR b.end   BETWEEN d.`datetime` AND DATE_ADD(d.`datetime`, INTERVAL 599 SECOND)
) AS not_available
ON all_slots.`datetime` = not_available.`datetime`
WHERE not_available.`datetime` is null