Design Hours of Operation SQL Table

Question

I am designing a SQL table to store hours of operation for stores.

Some stores have very simple hours: Monday to Sunday from 9:30AM to 10:00PM

Others are little 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 design the table(s)?

EDIT

The hours will be used to showing if a store is open at a user selected time.

A different table can probably handle any exceptions, such as holidays.

The store hours will not change from week to week.

Answer 1

A table like this would be easy for both the output you posted, as well as just firing a bit back (open? yes/no):

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

Features:

1. Using 24-hour isn't necessary, but makes math easier.
2. Store ID would presumably join to a lookup table where you stored Store information
3. Day ID would translate to day of week (1 = Sunday, 2 = Monday, etc.)

To query for your dataset, just: SELECT Day, Open, Close... (you'd want to format Open/Close obviously)

To query IsOpen?, just:

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

Answer 2

Think of it more as defining time frames, days / weeks are more complex, because they have rules and defined start and stops.

How would you define a timeframe?

one constraint (Start[Time and Day]), one reference 'Duration' (hours, minutes,.. of the span)*. Now the shifts (timeframes) can span multiple days and you don't have to work complex logic to extract and use the 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