13

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?

Simon
  • 4,395
  • 8
  • 33
  • 50

1 Answers1

31

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.

AdrianBR
  • 2,762
  • 1
  • 15
  • 29
  • 11
    how would you handle a night club case when they let's say open Fri 22:00 and close Sat 5:00? – Weltschmerz Apr 21 '15 at 16:02
  • For the generic case, a simple start datetime and end datetime will cover any possibilities. The example above is for a very specific case. so i would say table opening_times, columns(id, start_datetime, end_datetime), and create it for all forseeable future. If you want a weekly recurring and end up using weekday, just add another weekday column, such as start_weekday, start_hour, end_weekday, end_hour – AdrianBR Aug 04 '15 at 12:35
  • @AdrianBR You could store the endtime as the 5am timestamp and in code just detect if the endtime is less than the start time then assume it's the next day. – Matthew Auld Aug 24 '18 at 07:07
  • Alternatively if it's like a shop that is 24/hrs just have the start and end as `NULL` and in code detect for those instances. – Matthew Auld Aug 24 '18 at 07:08
  • 1
    Another way to handle the night club case could be to add a new interval: `shop ID, weekday, start_hour, end_hour` 1; 6; 22:00:00 ; 23:59:59 1; 7; 00:00:00 ; 05:00:00 that way would be easier to find "is open now?", but perhaps a bit more complex to "show all open hours". – Ernesto Rodríguez Ageitos Sep 20 '18 at 11:32