mysql - 如何从预约表中查找空闲时间段

Question

我正在尝试使用 AvailableTimeTable 和 AppointmentTable 列出空闲时间段。我的项目中已有 AppointmentTable，但我正在考虑添加 AvailableTimeTable 以便能够列出特定用户的空闲时间段。

可用时间表 (TAVT)

Id
Day (0 to 6)
Start time
End time

预约表(TAP)

Id 
Start datetime
End datetime

AvailableTimeTable 的数据示例 – 2016 年 3 月 7 日，星期一 - 这是用户 1 在星期一可用的时间 block

Id            day        start      end
1             1             08:00     10:00
2             1             10:15     12:00                         (15 min break here)
3             1             13:00     14:00  
4             1             14:15     16:00                         (15 min break here)

AppointmentTable 的数据示例 - 这是 user1 在 2016 年 3 月 7 日星期一的日程安排

Id            Start datetime               End datetime
1             2016-03-07-09:00             2016-03-07-10:00         (9-10 am)
2             2016-03-07-10:15             2016-03-07-11:00         (10:15-11am)

根据上述数据，当天(user1)的空闲时段为:

08:00 – 09:00
11:00 – 12:00
13:00 – 14:00
14:15 – 16:00

时间间隔总是 15 分钟的倍数

不确定查询应该是什么样子。我什至不确定我的 AvailableTimeTable 概念是否正确。也许它应该代表相反的，存储用户不可用的时间 block 。也许查询会更简单？

Answer 1

你可以从这里开始:

SELECT CASE
         WHEN time(ap.start) = av.start or time(ap.end) < av.end THEN time(ap.end)
         WHEN time(ap.start) > av.start or time(ap.end) = av.end THEN av.start
         ELSE av.start
       END available_start_time
     , CASE
         WHEN time(ap.start) = av.start or time(ap.end) < av.end THEN av.end
         WHEN time(ap.start) > av.start or time(ap.end) = av.end THEN time(ap.start)
         ELSE av.end
       END available_end_time
  FROM AvailableTimeTable av
    LEFT JOIN AppointmentTable ap
      ON date(ap.start) = :p_date
        AND weekday(ap.start)+1 = av.day
        AND time(ap.start) between av.start and av.end
        AND time(ap.end) between av.start and av.end

这将仅涵盖基本和受限情况(例如，每个可用时间行最多 2 个约会)。你能预约 15 分钟吗？你能有相邻的约会吗？我认为最好以 15 分钟为单位存储可用时间。否则，您应该运行一个游标来合并相邻的约会，然后再查找可用的时间 block 。