MySQL / PHP - 查找可用的时隙 [英] MySQL / PHP - Find available time slots

问题描述

有关类似主题的问题有几个问题，但我找不到这个具体问题的解决方案，感谢您的投入。

我正在努力一个预订引擎，用户可以在线预订某些服务。我停留在特定的一天（或一周）内查找和显示可用的时段。我知道所需时段的长度（例如1小时）以及时间段可预订的营业时间（例如09：00h - 18：00h）。另外我有一个MySQL表存储已经存在的约会。与我的问题相关的列如下所示：

  id start（datetime）end（datetime）
 1'2012 -11-16 13:00:002012-11-16 14:30:00
 22012-11-16 15:00:002012-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'
 ... 
  

现在给出提到的信息以上，我无法找到一个很好的解决方案（在MySQL或PHP中）来检索可用时隙的列表。还有一个条件：时隙只能按季度开始。也就是对于上面的第16个可用的一小时时间段的示例数据将是：
09:00，09:15，09:30，09:45，10:00，...，12： 00，16:00，16:15，...，17:00。



请注意，即使可能有重叠的时间（如样本数据）一个可用的时间段不能重叠。


你认为最好的方法是什么？

解决方案

  SELECT a.end AS free_after 
从订单a 
 WHERE NOT EXISTS（
 SELECT 1 
 FROM reservations 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; 
  

您只需要为 your_duration （整数）， start_of_search_window （日期时间）和 end_of_search_window （日期时间）。

如果你想要响铃和口哨....

  SELECT free_from，free_until 
 FROM（
 SELECT a.end AS free_from，
（SELECT MIN（c.start）
 FROM reservations c 
 WHERE c.start> a 。$）
从订单a 
 WHERE NOT EXISTS（
 SELECT 1 
 FROM reservations 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; 
  

将给你三个最短的可用插槽（即最接近目标大小）在窗口内。 p>

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?

解决方案

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.

这篇关于MySQL / PHP - 查找可用的时隙的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！