SQL table working hours calculation

Question

I am creating an SQL table to store opening hours for stores.

Some stores have very simple hours: Monday through Sunday from 9:30 to 22:00

Others are a bit more complicated. Please consider the following scenario:

Monday: Open All Day Tuesday: 7:30AM – 2:30PM & 4:15PM – 11:00 PM Wednesday: 7:00PM – 12:30 AM (technically closing on Thursday morning) Thursday: 9:00AM – 6:00PM Friday: closed. 

How would you create table (s)?

EDIT

The clock will be used to display if the store is open at a time chosen by the user.

Another table is likely to handle any exceptions, such as holidays.

Store hours will not change from week to week.

Answer 1

A table like this would be easy both for the output you output and for just firing the bit (open? Yes / no):

 Store | Day | Open | Closed --------------------------- 1 | 1 | 0000 | 2400 1 | 2 | 0730 | 1430 1 | 2 | 1615 | 2300 ... 

Features:

• Using a 24-hour clock is not required, but makes math easier.
• The store ID is supposed to join the lookup table where you saved the store information.
• The day identifier will be converted to the day of the week (1 = Sunday, 2 = Monday, etc.).

To query your data set, simply: SELECT Day, Open, Close... (preferably formatting Open / Close)

To request IsOpen ?, simply:

 SELECT CASE WHEN @desiredtime BETWEEN Open AND Closed THEN 1 ELSE 0 END FROM table WHERE store = @Store 

Answer 2

Think of it as defining a time frame; days / weeks are more complicated because they have rules and start and stop are defined.

How do you determine the timeframe?

one restriction (beginning [time and day]), one link "duration" (hours, minutes, .. range) *. Now shifts (timeframes) can span several days, and you do not need to work with complex logic to extract and use data in calculations.

 **Store_Hours** Store | Day | Open | DURATION --------------------------- 1 | 1 | 0000 | 24 1 | 2 | 0730 | 7 1 | 2 | 1615 | 6.75 ... 1 | 3 | 1900 | 5.5 

Answer 3

Do you need to do more than just store and display it?

I think that the design, which should determine whether the store is open at a certain time, should be informed by all the possibilities, otherwise you will not be able to come up with something.

What about holiday exceptions?

I would consider storing them as intervals based on base time (minutes from time 0 per week).

So, 0 is midnight on Monday.

Interval 1 will be 0 - 1440

Interval 2 will be 1890 - 2310

and etc.

You can easily convert a user-selected time into a minute offset and determine if the store is open.

The only remaining problem will be the interpretation on the display for a friendly display (perhaps some kind of extensive logic, but not impossible) and overlap during 10080 → 0.