Member Avatar for loligator

Hi all,

I want to store the hours of restaurants in a database (sounds simple, right?). I don't want a text field where hours are entered as a lump of data, but was thinking of making a column for each day of the week and filling in the hours this way. I was planning on doing something like this...

hours_sunday_open
hours_sunday_close
hours_monday_open
etc, etc,

Then I realized, what if the restaurant is open from say 4PM on Friday and doesn't close until 2am Saturday? This method won't work so well.

Any idea how I can store the open/close times taking the above problem into account?

It is not an option to just have a varchar or text column labeled hours. I'd much rather list each day separately for search purposes.

Thanks

  • 2 Contributors
  • 2 Replies
  • 204 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by loligator

Dani AI

Generated

Good question, — the column-per-day idea is simple but brittle (and it gets worse with multiple open/close intervals per day). pointed out a quick fix for the overnight case, which works for small sites. For a reliable, searchable design that scales, normalize hours into one row per open interval. That handles multiple shifts (lunch/dinner), overnight spans, and makes queries/indexes straightforward.

Example schema (MySQL-friendly):

CREATE TABLE opening_hours (
  id INT AUTO_INCREMENT PRIMARY KEY,
  restaurant_id INT NOT NULL,
  day_of_week TINYINT NOT NULL,    -- 0 = Sunday .. 6 = Saturday
  open_time TIME NOT NULL,
  close_time TIME NOT NULL,
  slot TINYINT DEFAULT 1,          -- ordering for multiple intervals
  INDEX (day_of_week, open_time, close_time),
  INDEX (restaurant_id, day_of_week)
);

Represent any overnight span as two intervals (one for the calendar day it opens, one for the early-hours of the next day). Example: Fri 16:00 -> Sat 02:00 becomes:

INSERT INTO opening_hours (restaurant_id, day_of_week, open_time, close_time) VALUES
(1, 5, '16:00:00', '23:59:59'),
(1, 6, '00:00:00', '02:00:00');

Lookup for a local timestamp is then trivial (after converting to the restaurant's local time): compute weekday and time, and find rows where day_of_week matches and open_time <= time AND close_time > time. Add a boolean or special flag for true 24/7 businesses, and store each restaurant's timezone (IANA name) so the app can convert query times correctly — daylight saving transitions are best handled in application code rather than by relying on TIME comparisons alone. This pattern keeps queries fast, is easy to maintain, and covers the edge cases you ran into.

Recommended Answers

All 2 Replies

Member Avatar for SoN9ne

There are a few different ways you can approach this.
You can still use your original concept, when validating the data for display, if the close value is less than the open, then obviously it is the next day. That seems to be the easiest, as long as the data has to stay in the db.

There are other ways but I think the above concept is sound.

Member Avatar for loligator

There are a few different ways you can approach this.
You can still use your original concept, when validating the data for display, if the close value is less than the open, then obviously it is the next day. That seems to be the easiest, as long as the data has to stay in the db.

There are other ways but I think the above concept is sound.

thanks for the input. i'll stick with my original idea.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.