MySQL - 查找可用时间段

Question

我正在开发一个预订系统，用户可以预订不同服务时长的服务。

我已经有一个存储预订信息的MySQL表格，一个可用时间段的表格和一个查询可以返回空闲时间段的结果。

我的“bookings”表格如下：

point_id     start (datetime)       end (datetime)
1            2017-06-17 14:50:00    2017-06-17 14:59:59
1            2017-06-17 15:10:00    2017-06-17 15:19:59
1            2017-06-17 15:40:00    2017-06-17 15:44:59
2            2017-06-17 15:50:00    2017-06-17 16:04:59
2            2017-06-17 16:05:00    2017-06-17 16:14:59
...

“datetimes” 表格包含每10分钟的时间间隔：

datetime (datetime)
2017-06-17 14:40:00
2017-06-17 14:50:00
2017-06-17 15:00:00
2017-06-17 15:10:00
2017-06-17 15:20:00
2017-06-17 15:30:00
2017-06-17 15:40:00
...

这是一个返回空闲时间段的查询：

SELECT
    a.datetime AS blockstart,
    DATE_ADD(a.datetime, INTERVAL 599 SECOND) AS blockend
FROM
    datetimes a
LEFT JOIN
    (
        SELECT *
        FROM bookings
        WHERE point_id = 1
    ) b ON
        (a.datetime BETWEEN b.start AND b.end)
        OR
        (DATE_ADD(a.datetime, INTERVAL 599 SECOND) BETWEEN b.start AND b.end)
WHERE b.id IS NULL AND a.datetime BETWEEN '2017-06-17 00:00:00' AND '2017-06-17 23:59:59';

如果服务持续时间少于25分钟，一切都没问题。问题出现在持续时间超过25分钟的服务上——我的查询会返回与已预订的10分钟服务重叠的时间段。例如，我已经预订了从13:00:00到13:09:59的服务，如果我查询1499秒的时间段，它会返回12:50:00，这将与下一个预订重叠。有人能帮我解决这个重叠服务的问题吗？

- t0niq

---

可能是mysql从表中显示可用时间槽和繁忙时间槽的重复问题。 - Evert

不，@Evert，你提供的链接只显示了像从11:00到14:20这样的时间间隔，但我的查询返回空闲的10分钟时间间隔，选择其中一个客户将被记录在所选的时间上。 - t0niq

我不明白...为什么要在日期时间表中添加9分钟59秒...是为了与预订表进行比较/连接吗？ - Dimitris Papageorgiou

Answer 1

您可以查找不可用时间段，然后与日期时间表重新连接以查找可用时间段：

SELECT distinct all_slots.`datetime`
FROM datetimes AS all_slots
LEFT JOIN (
  SELECT `datetime`
  FROM datetimes AS d
  JOIN bookings AS b
    ON b.start BETWEEN d.`datetime` AND DATE_ADD(d.`datetime`, INTERVAL 599 SECOND)
    OR b.end   BETWEEN d.`datetime` AND DATE_ADD(d.`datetime`, INTERVAL 599 SECOND)
) AS not_available
ON all_slots.`datetime` = not_available.`datetime`
WHERE not_available.`datetime` is null

- SIDU