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，这将与下一次预订重叠。谁能帮我解决这个服务重叠的问题？

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