mysql - 计算两天之间时间的 SQL 语句

Question

我有一个 MySql 数据库表，想要获取两个时间戳之间的所有记录。每次状态变化时，我都会得到一个新的时间戳和新的Status.

像这样：Status_List

 Status            Time_Start        Time_End
    2               14:00:12          14:13:33
    5               14:13:33          15:33:41
    9               15:33:41          16:02:11

当我搜索时：

select * from Status_List where Time_Start between (15:00:00 and 16:00:00)

Output: 9  15:33:41  16:02:11

但是我需要：

Output: 5 15:00:00  15:33:41
        9 15:33:41  16:00:00

这可能吗？

标签： mysqlsql

Answer 1

使用 UNION ALL：

set @start = '15:00:00', @end = '16:00:00';

select status, @start start, time_end end from status_list
where time_start = 
  (select max(time_start) from status_list where time_start <= @start)
union all
select status, time_start, time_end from status_list
where time_start > @start and time_end < @end 
union all
select status, time_start, @end from status_list
where time_end = 
  (select min(time_end) from status_list where time_end >= @end)

请参阅演示。
结果：

| status | start    | end      |
| ------ | -------- | -------- |
| 5      | 15:00:00 | 15:33:41 |
| 9      | 15:33:41 | 16:00:00 |