MySQL / PHP - search for available time intervals

Question

Several questions were asked on similar topics, but I can’t find a solution to this particular problem and would be grateful for your input.

I am working on a reservation mechanism where users can book certain services online. I am stuck in searching and displaying available time intervals for a specific day (or week). I know the length of the required time interval (for example, 1 hour) and the working hours in which you can reserve time intervals (for example, from 09:00 to 18:00). In addition, I have a MySQL table that stores existing meetings. The columns related to my questions are as follows:

id start (datetime) end (datetime) 1 '2012-11-16 13:00:00' '2012-11-16 14:30:00' 2 '2012-11-16 15:00:00' '2012-11-16 16:00:00' 3 '2012-11-17 12:00:00' '2012-11-16 15:45:00' 4 '2012-11-17 13:00:00' '2012-11-16 16:15:00' ... 

Now, given the information above, I cannot find a good solution (in MySQL or PHP) to get a list of the available time intervals. There is one more condition: time intervals can begin only quarterly. I.e. for the example above, on the 16th available one-hour time slots will be:
09:00, 09:15, 09:30, 09:45, 10:00, ..., 12:00, 16:00, 16:15, ..., 17:00.

Please note that even if the overlap time can be overlapped (as in the sample data), the available time interval cannot overlap.

What do you think is the best way to approach this?

Answer 1

 SELECT a.end AS free_after FROM bookings a WHERE NOT EXISTS ( SELECT 1 FROM bookings b WHERE b.start BETWEEN a.end AND a.end + INTERVAL your_duration HOURS ) AND a.end BETWEEN start_of_search_window AND end_of_search_window; 

you just need to specify the values ​​for your_duration (integer), start_of_search_window (date time) and end_of_search_window (date time).

And if you want bells and whistles ....

 SELECT free_from, free_until FROM ( SELECT a.end AS free_from, (SELECT MIN(c.start) FROM bookings c WHERE c.start > a.end) as free_until FROM bookings a WHERE NOT EXISTS ( SELECT 1 FROM bookings b WHERE b.start BETWEEN a.end AND a.end + INTERVAL your_duration HOUR ) AND a.end BETWEEN start_of_search_window AND end_of_search_window ) ORDER BY free_until-free_from LIMIT 0,3; 

You will get the three shortest available slots (i.e. the closest target size) in the window.