CODEWITHSUNDEEP
mysql
pufAmuf
pufAmuf

Reputation: 7805

Returning the future hours of operation

I'm trying to search for venues through an "Open Till at least" filter.

The issue I'm having is, that some venues operate through hours of 8AM to 6PM, and some from 10PM to 3AM. A Nice SO member helped me create this code, but when I tried to modify it for search, I noticed an issue.

If I query for a venue that's open till at least 20:00, and the venue opening times are from 11AM to 21:00, then everything is ok.

But if I query for a venue with opening hours till at least 20:00 and the operating hours are from 21:00 to 03:00, then closed is returned.

How can I show the hours of operation for venues which are still closed?

Here is my code:

SELECT
e.VENUE_NAME,
hrs.opening_day,
hrs.opening_time,
hrs.closing_time
FROM hours_of_operation hrs join venues e
  on hrs.venue_id = e.id
     and not is_closed
     and ((hrs.opening_day=date_format('$phpWhenDate' - INTERVAL (closing_time<opening_time and time('$phpWhenDate')<closing_time) DAY, '%a'))
          AND (closing_time<opening_time XOR
         (time('$phpWhenDate') >= least(opening_time,closing_time)
          and time('$phpWhenDate') < greatest(opening_time, closing_time))))

dataWhenDate looks like this: 2013-02-21 04:00:00

Basically, I use a slider to select how long I want the venue to be open till at least, and that generates a date to be sent.

Thanks everyone!

Upvotes: 0

Views: 50

Answers (1)

Zoltan Fedor
Zoltan Fedor

Reputation: 2097

Strictly speaking a venue which is open between 21:00 and 03:00, should really return as closed when querying opening hours till at least 20:00, because it is opening only at 21:00. If I would be the user it would actually mislead me if opening hours till at least 20:00 would show a venue which is only opening at 21:00.

If simply you want to show that which venues are open after 20:00 then just compare 20:00 with the opening and closing hours of the venue and if either is larger than 20:00 then that is open after 20:00 (but you might want to put in an asterisk explaining to the user that being open past 20:00 include venues which are opening after 20:00).

Upvotes: 1

Related Questions