Way to store various shop opening times in a database

Question

I want to store opening times for different shops in a database. At the moment I am working with the simplest solution:

CREATE TABLE opening_times(
 shop_id int(3) NOT NULL,
 times varchar(1000) NOT NULL
);

INSERT INTO opening_times VALUES(3,"Mon-Fri 8:30 to 18:00
Sat 9:00 to 12:00");

INSERT INTO opening_times VALUES(4,"24/7");

INSERT INTO opening_times VALUES(5,"Mon-Sun 8am-8pm");

My next idea for an enhancement was;

CREATE TABLE opening_times(
 shop_id int(3) NOT NULL,
 monday varchar(11) NOT NULL, 
 tuesday varchar(11) NOT NULL, 
 wednesday varchar(11) NOT NULL, 
 thursday varchar(11) NOT NULL, 
 friday varchar(11) NOT NULL, 
 saturday varchar(11) NOT NULL, 
 sunday varchar(11) NOT NULL
);

INSERT INTO opening_times VALUES(
 3,
 "09:30-18:30",
 "09:30-18:30",
 "09:30-18:30",
 "09:30-18:30",
 "09:30-18:30",
 "09:30-12:30",
 "CLOSED"
);

But this still leads to some problems:

• Days with multiple times are not possible. (8am to 11am & 1pm to 6pm)
• There is a lot of unused/redundant fields as many of my data sets have fixed times every day or are open 24/7
• They are not easy searchable.
• Holidays can not be represented.

So now I am wondering if there is a flexible way to store opening times. Maybe in a syntax like WD[1-5]{8-18},WD[6]{8-14},CD[12/25-12/26]{!0-24} where WD means weekday and CD represents a calender day or a range for exceptions and ! means closed.

Is there a common way to store the information like this?

Answer 1

Normalise your data

store it as

shop_ID, Weekday, Start_hour, end_hour

weekday can have values between 1 and 7, as an output of

SELECT DAYOFWEEK('2007-02-03')

start hour and end hour can be stored in time http://dev.mysql.com/doc/refman/5.0/en/time.html

with this you would have everything covered

To find hours on a date for a shop you would do

select start_hour, end_hour from table where weekday=dayofweek(curdate()) and shop_id=1

Need 2 time intervals for a day for a shop? no problem,

`shop ID, weekday, start_hour, end_hour`
1;         1;       08:00:00 ; 09:00:00
1;         1;       10:00:00 ; 11:00:00

For exceptions, you can add an exceptions table with the date and the shop. You can query that, and if it's null(no exception), return opening hours. Alternatively you can store every date for every shop, but that would bloat your data.