Check if a time is between two times (time DataType)

I had a very similar problem and want to share my solution

Given this table (all MySQL 5.6):

create table DailySchedule
(
  id         int auto_increment primary key,
  start_time time not null,
  stop_time  time not null
);

Select all rows where a given time x (hh:mm:ss) is between start and stop time. Including the next day.

Note: replace NOW() with the any time x you like

SELECT id
FROM DailySchedule
WHERE
  (start_time < stop_time AND NOW() BETWEEN start_time AND stop_time)
  OR
  (stop_time < start_time AND NOW() < start_time AND NOW() < stop_time)
  OR
  (stop_time < start_time AND NOW() > start_time)

Results

Given

  id: 1, start_time: 10:00:00, stop_time: 15:00:00
  id: 2, start_time: 22:00:00, stop_time: 12:00:00

• Selected rows with NOW = 09:00:00: 2
• Selected rows with NOW = 14:00:00: 1
• Selected rows with NOW = 11:00:00: 1,2
• Selected rows with NOW = 20:00:00: nothing

select *
from MyTable
where CAST(Created as time) not between '07:00' and '22:59:59 997'

I suspect you want to check that it's after 11pm or before 7am:

select *
from MyTable
where CAST(Created as time) >= '23:00:00' 
   or CAST(Created as time) < '07:00:00'