I need to retrieve data from a table from a given interval of time. My table is like this -

Given interval of time - (05:00:00 - 10:00:00)

My Expectation -

I need to get the id (4) as its start time in the given interval of time. So what will be the query?

so far I can imagine this -

CodePudding user response：

To get all the values between the given interval of time - (05:00:00 - 10:00:00) use:

select * 
from tbl
where Start_Time between '05:00:00' and '10:00:00'
or End_Time  between '05:00:00' and '10:00:00';

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ba828afe1a3ca9f21d6c104ea7dcfdff

Using comparison operator

SELECT * FROM tbl WHERE  
(`start_time`>= '05:00:00' AND `start_time` <= '10:00:00') OR 
 (`end_time`>= '05:00:00' AND `end_time` <= '10:00:00')