Droabs Droabs - 4 months ago 7
MySQL Question

Select all stores open at current time

I have a table named opening_hours that look like this:

id int(11), weekday int(1), start_hour time, end_hour time
- See image of possible data

I use this query to select all stores that are open now:

SELECT * FROM shops s
INNER JOIN opening_hours o
ON ( s.id = o.id )
AND ( o.weekday = WEEKDAY(CURDATE()) + 1 )
AND ( ( CURTIME() >= o.start_hour ) AND ( CURTIME() <= o.end_hour ) )


My problem is that this query is giving the wrong result when stores are open after midnight. That's because time after midnight is earlier than the time before midnight. How to handle this?

Answer

When the end_hour is less than start_hour then you have to modify the end_hour so that it becomes greater than start_hour.

One way to achieve this to deduct end_hour from 24:00:00 when end_hour is less than start_hour otherwise end_hour prevails.

SELECT * FROM shops s 
INNER JOIN opening_hours o 
ON ( s.id = o.id ) 
AND ( o.weekday = WEEKDAY(CURDATE()) + 1 ) 
AND ( ( CURTIME() >= o.start_hour ) AND ( CURTIME() <= IF(o.end_hour < o.start_hour,TIMEDIFF(TIME('24:00:00'),o.end_hour),o.end_hour ) ) ) ;