I have a mysql query issue

Question

My MySQL Query is OK with 22:29:50 But after 22:30:00 and end_time is next day 07:29:59 The Query is not working.

MY TABLE is shift

shift_name |start_time | end_time
shift1     | 07:30:00  |15:30:00
shift2     | 15:30:00  | 22:30:00
shift3     | 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