I am attempting to create a query to list open time slots for a specific day. I have found many similar questions on here, but none of the answers seem to do the job.
The query will need to only show results between a certain time period. For instance, if I wanted to search open time slots for the a single day I would set a start date of 2017-03-17 08:00:00 (8:00am being opening time) and an end date of 2017-03-17 17:00:00 (5:00pm being the closing time for the day). It would then have to query the day and return open slots and booked appointments from my appointments table. (Note: I do not save open slots in the table or a separate table, these will need to be generated by the query).
A simplified version of my booked appointments table:
Table name: booked_appointments (before output)
+----------------------+---------------------+----------+
| start_time | end_time | duration |
+----------------------+---------------------+----------+
| 2017-03-17 10:45:00 | 2017-03-17 11:30:00 | 45 |
| 2017-03-22 09:30:00 | 2017-03-22 10:30:00 | 60 |
| 2017-03-24 08:45:00 | 2017-03-24 11:30:00 | 165 |
| 2017-03-22 11:30:00 | 2017-03-22 12:30:00 | 60 |
+----------------------+---------------------+----------+
My current MYSQL query: (I pulled this from another answer and altered for my use)
SET @timeMinimum='2017-03-17 08:00:00';
SET @timeMaximum='2017-03-17 17:00:00';
SELECT IF (d.name = 'Free', @timeMinimum, b.start_time) AS free_from,
IF (d.name = 'Free', b.start_time, @timeMinimum := b.start_time + INTERVAL b.duration MINUTE) AS free_until,
d.name AS Free
FROM (SELECT 1 AS place, 'Free' AS NAME UNION SELECT 2 AS place, 'Booked' AS NAME ) AS d
INNER JOIN booked_appointments b
HAVING free_from < free_until
UNION SELECT @timeMinimum AS free_from, @timeMaximum AS free_until, 'Free' AS Free
FROM (SELECT 1) AS d
WHERE DATE(@timeMinimum) < DATE(@timeMaximum)
ORDER BY free_from, free_until;
The query does almost everything correctly except it ignores the timeMinimum and timeMaximum I have set and it instead returns results for the entire table that ranges from 2017-03-17 through 2017-03-24. This might not be an issue once I get query working for the day but it also is ignoring the last test date that I purposefully put out of order in the table to test a real world example of data entered.
The following is output from above query:
+---------------------+---------------------+--------+
| free_from | free_until | Free |
+---------------------+---------------------+--------+
| 2017-03-17 08:00:00 | 2017-03-17 10:45:00 | Free |
| 2017-03-17 10:45:00 | 2017-03-17 11:30:00 | Booked |
| 2017-03-17 11:30:00 | 2017-03-22 09:30:00 | Free |
| 2017-03-22 09:30:00 | 2017-03-22 10:30:00 | Booked |
| 2017-03-22 10:30:00 | 2017-03-24 08:45:00 | Free |
| 2017-03-22 11:30:00 | 2017-03-22 12:30:00 | Booked |
| 2017-03-24 08:45:00 | 2017-03-24 11:30:00 | Booked |
+---------------------+---------------------+--------+
Expected output is:
+---------------------+---------------------+--------+
| free_from | free_until | Free |
+---------------------+---------------------+--------+
| 2017-03-17 08:00:00 | 2017-03-17 10:45:00 | Free |
| 2017-03-17 10:45:00 | 2017-03-17 11:30:00 | Booked |
| 2017-03-17 11:30:00 | 2017-03-17 17:00:00 | Free |
+---------------------+---------------------+--------+
