MySQL / PHP - Find available time slots

Question

There have been a few questions asked on similar topics but I cannot find a solution to this specific problem and would be thankful for your input.

I am working on a booking engine where users can book certain services online. I am stuck on finding and displaying available time slots within a specific day (or week). I know the length of the needed time slot (eg. 1 hour) and the business hours in which time slots are bookable (eg. 09:00h - 18:00h). Additionally I have a MySQL table that stores the already existing appointments. The columns relevant to my questions look like this:

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 mentioned above, I am unable to find a good solution (in MySQL or PHP) for retrieving a list of available time slots. There is one more condition: Time slots can only start in quarterly steps. Ie. for the example data above on the 16th available one hour time slots would be:
09:00, 09:15, 09:30, 09:45, 10:00, ..., 12:00, 16:00, 16:15, ..., 17:00.

Please note that even though there may be overlapping times (as in sample data) an available time slot cannot overlap.

What would 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 supply 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;

Will give you the three shortest available slots (i.e. nearest the target size) within the window.