Problem #1:

How can the table be designed in MySQL in efficient way to structure

1) Store Opening Time, 
2) Break Time and 
3) Closing Time in a day

For an instance, On Sunday: Opening Time: 7:00AM, Closing Time: 5:00PM, Break Time Start: 1:00PM, Break Time End: 2:30PM.

Problem #2:

How to define the query to search that table for all Stores that are being Opened/Closed between the given range.

  • Have you actually tried to do this yourself? What table structure do you have? What query do you have? I guess this is homework? – Mr.Brownstone Dec 20 '15 at 18:03
  • I've tried designing a table structure: (store_id, day, opening_hours (VARCHAR(24)) that takes either 0 -Closed, 1-Opened for each hours. For eg. 000000111111101110000000. Here, 1 in 7th place represents Open at 7:00AM – Sujit Baniya Dec 22 '15 at 8:35

From your comment above I cannot understand your motive for how you are trying to achieve it the way that you are but this is how I would do it (this is off the top of my head and I have not had chance to test this yet so please bear that in mind):

    -- additional columns for the store type
    PRIMARY KEY (id)
CREATE TABLE `OpeningHours`
    store_id INT NOT NULL,
    `open` TIME NOT NULL,
    `close` TIME NOT NULL,
    FOREIGN KEY (`store_id`) REFERENCES `stores`(`id`)
    -- add your own indexes here

Then put your open to close times in here such that if you opened at 9, closed at 5 and took a 1 hour lunch at 12 you would have data like so:

stored_id    `open`    `close`
1             09:00:00  12:00:00
1             13:00:00  17:00:00

Then you can query for stores that are open between a range of times like so:

    SET p_Time = TIME();

   FROM `OpeningHours`
  WHERE  p_Time BETWEEN `open` AND `close`;

You can then use the store_id from the results above to join to the store table to get the store details.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.