mysql - 我有一个 mysql 查询问题

Question

我的 MySQL 查询在 22:29:50 是正常的，但是在 22:30:00 之后并且 end_time 是第二天的 07:29:59 查询不起作用。

我的表是 shift

shift_name |start_time | end_timeshift1     | 07:30:00  |15:30:00shift2     | 15:30:00  | 22:30:00shift3     | 22:30:00  | 07:30:00

I write the following query

select shift_name from shift 
where time('22:30:00') BETWEEN start_time and end_time;

Answer 1

CREATE TABLE shift 
    (`shift_name` varchar(6), `start_time` time, `end_time` time);

INSERT INTO shift 
    (`shift_name`, `start_time`, `end_time`)
VALUES
    ('shift1', '07:30:00', '15:30:00'),
    ('shift2', '15:30:00', '22:30:00'),
    ('shift3', '22:30:00', '07:30:00');

select 
     *
from (
     select cast('05:22' as time) t
    ) d
cross join shift
where (t >= start_time and t < end_time) 
or  (t >= cast('00:00' as time) and t < end_time and start_time > end_time ) 
or  (t >= start_time and start_time > end_time )

t        | shift_name | start_time | end_time:------- | :--------- | :--------- | :-------05:22:00 | shift3     | 22:30:00   | 07:30:00

select 
     *
from (
     select cast('23:52' as time) t
    ) d
cross join shift
where (t >= start_time and t < end_time) 
or  (t >= cast('00:00' as time) and t < end_time and start_time > end_time ) 
or  (t >= start_time and start_time > end_time )

t        | shift_name | start_time | end_time:------- | :--------- | :--------- | :-------23:52:00 | shift3     | 22:30:00   | 07:30:00

select 
     *
from (
     select cast('10:52' as time) t
    ) d
cross join shift
where (t >= start_time and t < end_time) 
or  (t >= cast('00:00' as time) and t < end_time and start_time > end_time ) 
or  (t >= start_time and start_time > end_time )

t        | shift_name | start_time | end_time:------- | :--------- | :--------- | :-------10:52:00 | shift1     | 07:30:00   | 15:30:00

select 
     *
from (
     select cast('19:52' as time) t
    ) d
cross join shift
where (t >= start_time and t < end_time) 
or  (t >= cast('00:00' as time) and t < end_time and start_time > end_time ) 
or  (t >= start_time and start_time > end_time )

t        | shift_name | start_time | end_time:------- | :--------- | :--------- | :-------19:52:00 | shift2     | 15:30:00   | 22:30:00

dbfiddle here