php - 在 mysql 和 php 中寻找空闲时间 block ？

Question

我在 mysql 中有一个这样的表:

+------------+------------+------------+------------+ 
|     date   |   user_id  | start_hour | end_hour   | 
+------------+------------+------------+------------+ 
| 2010-12-15 |         20 | 08:00:00   | 08:15:00   | 
| 2010-12-15 |         20 | 14:00:00   | 14:30:00   | 
| 2010-12-15 |         20 | 17:00:00   | 17:45:00   | 
+------------+------------+------------+------------+ 

我尝试提取用户时间的时间范围我找到了例子 here , 但我不能按小时完成这项工作

我试过查询:

 $sql="
SELECT a.end_hour AS 'Available From', Min(b.start_hour) AS 'To' 

FROM ( 
SELECT  0 as date, '08:00:00' as start_hour,'08:00:00' as end_hour 
UNION SELECT date, start_hour, end_hour FROM table
) 

AS a JOIN 
( SELECT  date, start_hour, end_hour  FROM table 
UNION SELECT 0,  '21:00:00' as start_hour, '22:00:00' as end_hour
) AS b ON

a.date=b.date AND  a.user_id=b.user_id AND a.end_hour < b.start_hour WHERE  
a.date='$date'  AND a.user_id='$user_id' GROUP BY a.end_hour 
HAVING a.end_hour < Min(b.start_hour);"; 

我需要创建一个从 08:00 到 21:00 的范围，其中包含约会之间的空闲时段像这样:

free time
08:15:00 to 14:00:00
14:30:00 to 17:00:00
17:45:00 to 21:00:00

Answer 1

试试这个查询

SELECT
  a.id,
  a.start_hour,
  a.end_hour,
  TIMEDIFF(la.start_hour, a.end_hour) as `Free Time`
FROM appointment as a
  LEFT JOIN(SELECT * FROM appointment LIMIT 1,18446744073709551615) AS la
    ON la.id = a.id + 1
  LEFT JOIN (SELECT * FROM appointment) AS ra ON a.id = ra.id

这将显示这些结果

+---------------------------------------------+
¦ id ¦ start_hour BY ¦ end_hour   | Free Time |
¦----+---------------¦------------------------|
¦  1 ¦   08:00:00    ¦  08:15:00  | 05:45:00  |
¦  2 ¦   14:00:00    ¦  14:30:00  | 02:30:00  |
¦  3 ¦   17:00:00    ¦  17:45:00  | 03:15:00  |
¦  4 ¦   21:00:00    ¦  21:00:00  | (NULL)    |
+--------------------+------------------------+ 

此外，表中必须包含 21:00:00，否则将无法获取最后的时差。我在表中输入 21:00:00 作为开始和结束日期。

已编辑

这是修改后的查询

SELECT
  a.id,
  a.end_hour AS `Free time Start`,
  IFNULL(la.start_hour,a.end_hour) AS `Free Time End`,
  IFNULL(TIMEDIFF(la.start_hour, a.end_hour),'00:00:00') AS `Total Free Time`
FROM appointment AS a
  LEFT JOIN (SELECT * FROM appointment LIMIT 1,18446744073709551615) AS la
    ON la.id = (SELECT MIN(id) FROM appointment where id > a.id LIMIT 1)      

结果是

+--------------------------------------------------------+
¦ id ¦ Free time Start ¦ Free Time End | Total Free Time |
¦----+-----------------¦---------------------------------|
¦  1 ¦   08:15:00      ¦ 14:00:00      |    05:45:00     |
¦  2 ¦   14:30:00      ¦ 17:00:00      |    02:30:00     |
¦  3 ¦   17:45:00      ¦ 21:00:00      |    03:15:00     |
¦  4 ¦   21:00:00      ¦ 21:00:00      |    00:00:00     |
+----------------------+---------------------------------+  

从这个查询中学习的要点是

1. Timediff 函数的使用。 timediff('结束时间','开始时间')
2. 加入上位
3. 避免在 join 中使用长偏移量和限制从 1 而不是 0 开始的第一个记录
4. IFNULL 用法 ifnull('if here comes null','select this then')