Select time between two times mysql
mysql, sql
Solution
There is not a great way performance-wise to handle this. The correct `where` statement is:
where (start <= end and :time between start and end) or
(end < start and :time not between end and start)
Of course, you don't have to use `between`, you can expand this out:
where (start <= end and :time >= start and :time <= end) or
(end < start and (:time <= end or :time >= start))
Problem
I have to check if a specific time is between two times (opening and closing time). The opening and closing times are saved in a database as "start" and "end" related to a weekday id and not to a date. So my problem is, that I have two different cases first case is that the end-time is bigger than start time like if end=19:00:00 and start=09:00:00 but it also can be end=06:00:00 and start=20:00:00 So what is a performance way to check if a time is between the two times? My sql looks like this: ``` SELECT * FROM opening WHERE weekday_id = :weekday AND start <= :time AND end >= :time ```