CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designlogic
Droabs
Droabs

Reputation: 119

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

enter image description here

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?

Upvotes: 3

Views: 248

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

The correct logic is more complicated. When the opening hours span two dates, then in the wee hours of the morning, you have to look at the previous days opening hours -- unless you assume that the hours are the same for each day (but then why have table?).

The condition is more like this:

SELECT *
FROM shops s INNER JOIN
     opening_hours o 
     ON s.id = o.id
WHERE ((o.weekday = weekday(curdate()) + 1) and
       ((o.end_hour > o.start_hour and curtime() >= o.start_hour and curtime <= o.end_hour) or
        (o.end_hour < o.start_hour and curtime() >= o.start_hour)
       ) or
      (o.weekday = weekday(date_add(curdate(), interval 1 day))  + 1 and
       o.end_hour < o.start_hour and
       curtime() <= o.end_Hour
      );

The three conditions are:

  • Opening hours all in one day
  • Opening hours span two days, and the hour not earlier than the opening
  • Opening hours span two days, and the hour is earlier than the closing hour

Upvotes: 2

1000111
1000111

Reputation: 13519

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

EDIT: The query above performs great when the end_hour is 00:00:00.

But it might give wrong output for this case

start_hour = 07:00:00 & end_hour = 02:00:00.

So, here how you can recover (use this condition in your main query):

AND 
( 
  IF(o.end_hour < o.start_hour, 
  ( CURTIME() >= o.start_hour ) OR ( CURTIME() <= o.end_hour ),
  ( CURTIME() >= o.start_hour ) AND ( CURTIME() <= o.end_hour )
)

Note that if the range lies in the same date then this condition should prevail:

( CURTIME() >= o.start_hour ) AND ( CURTIME() <= o.end_hour ).

And if the range wraps over midnight then this condition should be in action:

( CURTIME() >= o.start_hour ) OR ( CURTIME() <= o.end_hour )

Upvotes: 2

Related Questions