Read 553 times | Created 2013-07-11 08:01:59 | Updated 2013-07-11 08:01:59 | | |

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 HOURS
)
AND a.end BETWEEN start_of_search_window AND end_of_search_window
)
ORDER BY free_until-free_from
LIMIT 0,3;