Reputation: 119
I have a table named opening_hours that look like this:
id int(11), weekday int(1), start_hour time, end_hour time
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
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:
Upvotes: 2
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