CODEWITHSUNDEEP
mysqlsql
Kingalione
Kingalione

Reputation: 4275

Select time between two times mysql

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

Upvotes: 2

Views: 9194

Answers (2)

albe
albe

Reputation: 551

If the times are in the same day and you don't know which is the start and which is the end (seems odd) then:

SELECT * 
FROM opening
WHERE weekday_id = :weekday
  AND (:time BETWEEN start AND end OR :time BETWEEN end AND start)

If the times include the date (datetime) and you know which is which then you just need one between.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271191

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))

Upvotes: 12

Related Questions