Mysql在预约系统的约会之间查找空闲时间（插槽） [英] Mysql Find free time (slots) between appointments for booking system

问题描述

我正在尝试找到2个datetime字段之间的空闲时间段，这个时间段与预订系统的特定时间段相匹配，但是我正在查询正确。

I'm trying to find the free time slots between 2 datetime field that match a specific duration for a booking system, but I'am stuck on the correct query.

我的db事件：

+----------+---------------------+---------------------+---------+
| Event_ID | StartTime           | EndTime             | Pers_ID |
+----------+---------------------+---------------------+---------+
|        3 | 2013-09-21 16:00:00 | 2013-09-21 16:30:00 |       6 | 
|       21 | 2013-09-21 09:00:00 | 2013-09-21 09:15:00 |       6 | 
|        5 | 2013-09-21 09:15:00 | 2013-09-21 10:15:00 |       6 | 
|       64 | 2013-09-21 15:00:00 | 2013-09-21 15:45:00 |       6 | 
|       32 | 2013-09-21 10:15:00 | 2013-09-21 11:30:00 |       6 | 
|        6 | 2013-09-21 13:00:00 | 2013-09-21 14:45:00 |       6 | 
+----------+---------------------+---------------------+---------+

结果必须是在2013-09-21 09:00:00和2013-09-21 22:00:00之间的Pers_ID = 6的75分钟的空闲可能的插槽，并且他们必须在EndTime已经在数据库

The result must be the free possible slots of 75 minutes with Pers_ID=6 between 2013-09-21 09:00:00 AND 2013-09-21 22:00:00 , and also they have to start right after an EndTime already in the database

已经感谢您的帮助，

E-raser

Already thanks for the help,
E-raser

推荐答案

SELECT AvailStartTime, AvailEndTime
FROM (
    SELECT @lastEndTime as AvailStartTime, StartTime as AvailEndTime, @lastEndTime := EndTime
    FROM (SELECT StartTime, EndTime
          FROM Events
          WHERE Pers_ID = 6
            AND EndTime >= '2013-09-21 09:00'
            AND StartTime < '2013-09-21 22:00'
          ORDER BY StartTime) e
    JOIN (SELECT @lastEndTime := NULL) init) x
WHERE AvailEndTime > DATE_ADD(AvailStartTime, INTERVAL 75 MINUTE)

演示

这篇关于Mysql在预约系统的约会之间查找空闲时间（插槽）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！