MySQL - finding available time slots
I am working on a booking system where users should be able to book services with different service durations.
I already have a MySQL table that stores orders, a table with available timeslots, and a query that returns free timeslots.
My table bookings
looks like this:
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
the table contains 10 minute 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 a query that returns free 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 time is less than 25 minutes, everything is fine. The problem is that services are longer than 25 minutes - my request returns slots that overlap the already booked 10 minute services. For example, I have a booking from 13:00 to 13:09:59, if I request an interval of 1499 seconds, it will return 12:50:00, which will overlap the next order. Can anyone help me solve this overlapping services issue?
source to share
You can find unavailable timeslots and then join the 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
source to share