SQL/MySQL查询可用的日期和时间在数据库

Question

我需要一些帮助查询我的日历/日期表

情景：我有一个“日历”表的日期和时间（见下文），用户将设置其可用日期，通常每天都有可用的时间段。所以，我的表看起来像这样：

+------+------------+---------------------+---------------------+ 
| ID | user_id | start_date   | end_date   | 
+------+------------+---------------------+---------------------+ 
| 1 |   1 | 2016-09-01 08:00:00 | 2016-09-01 16:00:00 | 
| 2 |   1 | 2016-09-03 00:00:00 | 2016-09-03 23:59:59 | 
| 3 |   1 | 2016-09-04 00:00:00 | 2016-09-04 16:00:00 | 
| 4 |   1 | 2016-09-05 08:00:00 | 2016-09-05 16:00:00 | 
| 5 |   2 | 2016-09-05 08:00:00 | 2016-09-05 16:00:00 | 
| 6 |   2 | 2016-09-07 08:00:00 | 2016-09-07 16:00:00 | 
| 7 |   2 | 2016-09-08 08:00:00 | 2016-09-08 16:00:00 | 
| 8 |   2 | 2016-09-08 18:00:00 | 2016-09-08 22:00:00 | 
+------+------------+---------------------+---------------------+ 

我们有2个用户在这里，所以我想以下几点：

• 如果我要寻找的起始日期= 2016年9月5日08:00:00日期和结束日期= 2016-09-05 16:00:00它应该返回用户1和2.因为他们都有这些日期的条目。同样如果start_date = 2016-09-05 09:00:00和end_date = 2016-09-05 15:00:00，这也应该返回两个用户，因为搜索时间在时间​​段之间为如示例中所示。

• 第二种情况是有点棘手，如果用户搜索start_date = 2016-09-03 08:00:00和end_date = 2016-09-04 16:00:00我希望查询检查如下：

  • 看看用户每天在这些时间是否可用。
  • 所以在这种情况下，用户可以在2016年9月3日的08:00:00和16:00:00之间以及2016年9月4日的08:00:00和16:00之间使用： 00。
  • 在这个例子在这个应该返回用户1.

即时通信开放上重新设计我的架构，如果需要的建议。

希望有人能帮助我。

Answer 1

DEMO包含一些辅助代码注释，并显示查询的演变过程。另外，我还为user_id = 2添加了另一行，以显示如何匹配范围内的两天中的一个。

SELECT U.`user_id` 
FROM (
     select a.selectDate, 
       CONCAT(a.selectDate, ' ', time(@s_date)) as start_time, 
       CONCAT(a.selectDate, ' ', time(@s_date)) as end_time 
     from (
      select '1900-01-01' + INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) + (10000 * e.a)) DAY as selectDate 
      from  (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a 
      cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b 
      cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c 
      cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d 
      cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as e 
     ) a 
     CROSS JOIN (SELECT @s_date := '2016-09-03 08:00:00', @e_date := '2016-09-04 16:00:00') par 
     -- CROSS JOIN (SELECT @s_date := '2016-09-05 08:00:00', @e_date := '2016-09-05 16:00:00') par 
     -- CROSS JOIN (SELECT @s_date := '2016-09-05 09:00:00', @e_date := '2016-09-05 15:00:00') par 
     WHERE selectDate BETWEEN date(@s_date) 
          AND date(@e_date) 
    ) D 
CROSS JOIN (SELECT DISTINCT `user_id` FROM Table1) U 
LEFT JOIN Table1 T 
     ON U.`user_id` = T.`user_id` 
     AND D.start_time <= T.`end_date` 
     AND D.end_time >= T.`start_date` 
GROUP BY U.`user_id` 
HAVING COUNT(U.`user_id`) = COUNT(T.`user_id`); 

OUTPUT

• 步骤1：创建日期的列表，在这种情况下273年
• 步骤2：选择范围之间的所有的日期中的参数限定，还包括时间窗口到每个日期。
• 第3步：加入一起看什么日期有用户在时间窗口
• 第4步：选择一个时间窗口只有用户所有日期