CODEWITHSUNDEEP
phpmysqldatabaseschema
Gimme the 411
Gimme the 411

Reputation: 1044

A way to store opening hours in order to determine whether a place is open now

I want to be able to store opening hours for multiple restaurants and determine whether a store is open now, but I'm stuck on how to deal with places that close after midnight.

Here's an example of a restaurant's opening hours:

Monday: 6:00AM-10:00PM
Tuesday: 6:00AM-10:00PM
Wednesday: 6:00AM-10:00PM
Thursday: 6:00AM-10:00PM
Friday: 6:00AM-11:59PM
Saturday: 0:00AM-3:00AM, 6:00AM-11:59PM
Sunday: 0:00AM-3:00AM, 6:00AM-10:00PM

I'm not sure what type of schema I should use to represent this, in a way that will allow me to determine whether a particular restaurant is open now.

When I have the schema set up, my goal is to be able to determine the following for any specific restaurant:

So if the current time is 15 minutes before the restaurant opens, the restaurant should be considered open, and if the current time is 45 minutes before closing, the restaurant should be considered closed, and anything between that should be considered open, taking into account rollover at midnight.

So here are some expected inputs and results:

Restaurant 1
Monday: 6:00AM-10:00PM
Tuesday: 6:00AM-10:00PM
Wednesday: 6:00AM-10:00PM
Thursday: 6:00AM-10:00PM
Friday: 6:00AM-11:59PM
Saturday: 0:00AM-3:00AM, 6:00AM-11:59PM
Sunday: 0:00AM-3:00AM, 6:00AM-10:00PM

Current day/time -> Result
Monday 12:00PM -> Open
Monday 2:00AM -> Closed
Monday 5:45AM -> Open
Monday 9:30PM -> Closed
Saturday 4:00AM -> Closed
Saturday 11:55PM -> Open
Sunday 2:00AM -> Open
Sunday 2:25AM -> Closed

I'm not sure how I would deal with the rollover times, for example to determine whether the restaurant will be open 45 minutes from now on Saturday at 11:55PM.

Here's another example of a restaurant's opening hours:

Monday: 10:00AM-10:00PM
Tuesday: 10:00AM-10:00PM
Wednesday: 10:00AM-10:00PM
Thursday: 10:00AM-10:00PM
Friday: 10:00AM-11:59PM
Saturday: 0:00AM-11:59PM
Sunday: 0:00AM-9:00PM

On Saturday the store is open for 24 hours, so I need to be able to deal with this case too.

A restaurant could be open 24/7:

Monday: 0:00AM-11:59PM
Tuesday: 0:00AM-11:59PM
Wednesday: 0:00AM-11:59PM
Thursday: 0:00AM-11:59PM
Friday: 0:00AM-11:59PM
Saturday: 0:00AM-11:59PM
Sunday: 0:00AM-11:59PM

A restaurant could have 0, 1, 2, or more periods per day:

Monday: CLOSED
Tuesday: CLOSED
Wednesday: 10:00AM-2:00PM, 5:00PM-10:00PM
Thursday: 10:00AM-2:00PM, 5:00PM-10:00PM
Friday: 10:00AM-2:00PM, 5:00PM-11:59PM
Saturday: 0:00AM-3:00AM, 10:00AM-2:00PM, 5:00PM-11:59PM
Sunday: 0:00AM-3:00AM, 10:00AM-2:00PM, 5:00PM-9:00PM

So my goal is to find, for a given restaurant and its opening hours, whether the current time is between:

And to find a good way to store the information so that it is possible to determine that, for multiple restaurants.

Because there could be 0 or multiple periods per day, I think it makes sense to store 1 period per row, but beyond that I am not sure how to do it.

Upvotes: 1

Views: 714

Answers (1)

Strawberry
Strawberry

Reputation: 33935

I think I might be tempted to store it this way:

bit_code opens    closes
69       06:00:00 22:00:00 
48       06:00:00 03:00:00

I've represented the days of the week numerically, as follows:

Monday    -  1
Tuesday   -  2
Wednesday -  4
Thursday  -  8
Friday    - 16
Saturday  - 32
Sunday    - 64

So, Friday + Saturday = 48

EDIT:

Consider the following example:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(weekday_code INT NOT NULL
,opens TIME NOT NULL
,closes TIME NOT NULL
);

INSERT INTO my_table VALUES
(69,'06:00:00','22:00:00'),
(48,'06:00:00','03:00:00');

It's 23:30:00. I think the following will tell us which days of the week the restaurant is currently open (and considering the 15-minute and 45-minute rules)...

SELECT *  
  FROM my_table 
 WHERE CAST('23:30:00' AS TIME) >= opens - INTERVAL 15 MINUTE
   AND CAST('23:30:00' AS TIME) <= (CASE WHEN closes < opens THEN closes + INTERVAL 24 HOUR ELSE closes END) - INTERVAL 45 MINUTE; 

+--------------+----------+----------+
| weekday_code | opens    | closes   |
+--------------+----------+----------+
|           48 | 06:00:00 | 03:00:00 |
+--------------+----------+----------+

We can introduce other tricks - either in application code, or SQL - to decipher to which days of the week '48' refers. Equally, we don't have to store days of the week this way; it just seems quite compact, especially if there are lots of restaurants (with fairly consistent opening hours).

Anyway, if adopting this approach... and just for fun/completeness...

SELECT a.x 
FROM 
   ( SELECT  1 x UNION
     SELECT  2 UNION
     SELECT  4 UNION
     SELECT  8 UNION
     SELECT 16 UNION
     SELECT 32 UNION
     SELECT 64 
   ) a
JOIN my_table b
  ON a.x = ( a.x & b.weekday_code) 
 AND CAST('23:30:00' AS TIME) >= b.opens - INTERVAL 15 MINUTE
 AND CAST('23:30:00' AS TIME) <= (CASE WHEN b.closes < b.opens THEN b.closes + INTERVAL 24 HOUR ELSE b.closes END) - INTERVAL 45 MINUTE; 

 +----+
 | x  |
 +----+
 | 16 |
 | 32 |
 +----+

We can infer from this that if today is either Friday or Saturday, then the restaurant is open.

Note that there may be a more elegant way of expressing this in SQL - or it may be smarter to handle the bit-decoding in application code,

Upvotes: 1

Related Questions