MySQL不在某个时间范围内,如何在指定的时间范围内在mysql中找到结果？

My table

ID StartTime EndTime

1    09:00:00   10:00:00

2    10:30:00   11:00:00

3    14:30:00   16:00:00

How to check the if passed date ranges fall in between the Start and End Times...like when i say, if starttime is 09:24:00 and endtime is 09:56:00? it should return Id value of 1

Can you some please help me with this?

解决方案

Use BETWEEN if a value is between two other values. Note that BETWEEN comparison includes the borders.

WHERE `time` BETWEEN `StartTime` AND `EndTime`

Of course don't forget to

CAST(`time` AS TIME)

if it it isn't of the type TIME.

To check for a range:

WHERE

(CAST(`rangeStart` AS TIME) BETWEEN `StartTime` AND `EndTime`) AND

(CAST(`rangeEnd` AS TIME) BETWEEN `StartTime` AND `EndTime`)