mysql - 如何在 MySQL 中获取 2 个给定字段之间的间隔时间

Question

我需要下表，其中 start_time 和 end_time 是 TIME 字段:

 ____________________________________
| id | day | start_time |  end_time  |
|____|_____|____________|____________|
|  1 | 1   |    8:00    |    12:00   |

我想获取start_time和end_time之间每60分钟间隔的开始时间，如:

 _______
| start |
|_______|
|  8:00 |
|  9:00 |
| 10:00 |
| 11:00 |

这可能吗？谢谢

Answer 1

如果您有一个包含整数的表，这会更容易。如果该字段是日期时间，您可以这样做(最多 7 小时):

select date_add(t.start_time, interval n.n hour)
from t join
     (select 0 as n 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
     ) n
     on date_add(t.start_time, interval n.n hour) <= t.end_time

对于 time，您可以通过转换为秒并在那里进行算术来实现:

select sec_to_time((floor(time_to_sec(t.start_time - 1)/3600)+1) + n.n*3600)
from t join
     (select 0 as n 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
     ) n
     on (floor(time_to_sec(t.start_time - 1)/3600)+1) + n.n*3600 <= t.end_time