Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How would you store and query hours of operation?

We're building an app that stores "hours of operation" for various businesses. What is the easiest way to represent this data so you can easily check if an item is open?

Some options:

  • Segment out blocks (every 15 minutes) that you can mark "open/closed". Checking involves seeing if the "open" bit is set for the desired time (a bit like a train schedule).
  • Storing a list of time ranges (11am-2pm, 5-7pm, etc.) and checking whether the current time falls in any specified range (this is what our brain does when parsing the strings above).

Does anyone have experience in storing and querying timetable information and any advice to give?

(There's all sorts of crazy corner cases like "closed the first Tuesday of the month", but we'll leave that for another day).

like image 996
Kalid Avatar asked Sep 26 '08 21:09

Kalid


2 Answers

store each contiguous block of time as a start time and a duration; this makes it easier to check when the hours cross date boundaries

if you're certain that hours of operation will never cross date boundaries (i.e. there will never be an open-all-night sale or 72-hour marathon event et al) then start/end times will suffice

like image 145
Steven A. Lowe Avatar answered Oct 05 '22 23:10

Steven A. Lowe


The most flexible solution might be use the bitset approach. There are 168 hours in a week, so there are 672 15-minute periods. That's only 84 bytes worth of space, which should be tolerable.

like image 42
Greg Hewgill Avatar answered Oct 05 '22 22:10

Greg Hewgill