How would you store and query hours of operation?

Question

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).

Answer 1

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

Answer 2

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.

Answer 3

The segment blocks are better, just make sure you give the user an easy way to set them. Click and drag is good.

Any other system (like ranges) is going to be really annoying when you cross the midnight boundary.

As for how you store them, in C++ bitfields would probably be best. In most other languages, and array might be better (lots of wasted space, but would run faster and be easier to comprehend).

Answer 4

There is surely no need to conserve memory here, but perhaps a need for clean and comprehensible code. "Bit twiddling" is not, IMHO, the way to go.

We need a set container here, which holds any number of unique items and can determine quickly and easily whether an item is a member or not. The setup reuires care, but in routine use a single line of simply understood code determines if you are open or closed

Concept: Assign index number to every 15 min block, starting at, say, midnight sunday.

Initialize: Insert into a set the index number of every 15 min block when you are open. ( Assuming you are open fewer hours than you are closed. )

Use: Subtract from interesting time, in minutes, midnight the previous sunday and divide by 15. If this number is present in the set, you are open.

Answer 5

I think I'd personally go for a start + end time, as it would make everything more flexible. A good question would be: what's the chance that the block size would change at a certain point? Then pick the solution that best fits your situation (if it's liable to change I'd go for the timespans definately).

You could store them as a timespan, and use segments in your application. That way you have the easy input using blocks, while keeping the flexibility to change in your datastore.

Answer 6

I would think a little about those edge-cases right now, because they are going to inform whether you have a base configuration plus overlay or complete static storage of opening times or whatever.

There are so many exceptions - and on a regular basis (like snow days, irregular holidays like Easter, Good Friday), that if this is expected to be a reliable representation of reality (as opposed to a good guess), you'll need to address it pretty soon in the architecture.

Answer 7

I'd use a table like this:

BusinessID | weekDay | OpenTime | CloseTime 
---------------------------------------------
     1          1        9           13
     1          2        5           18
     1          3        5           18
     1          4        5           18
     1          5        5           18
     1          6        5           18
     1          7        5           18

Here, we have a business that has regular hours of 5 to 6, but shorter hours on sunday.

A query for if open would be (psuedo-sql)

SELECT @isOpen = CAST
   (SELECT 1 FROM tblHours 
       WHERE BusinessId = @id AND weekDay = @Day 
       AND CONVERT(Currentime to 24 hour) IS BETWEEN(OpenTime,CloseTime)) AS BIT;

If you need to store edge cases, then just have 365 entries, one per day...its really not that much in the grand scheme of things, place an index on the day column and businessId column.

Don't forget to store the businesses timezone in a separate table (normalize!), and perform a transform between your time and it before making these comparisons.

Answer 8

To add to what Johnathan Holland said, I would allow for multiple entries for the same day.

I would also allow for decimal time, or another column for minutes.

Why? many restaurants and some businesses, and many businesses around the world have lunch and or afternoon breaks. Also, many restaurants (2 that I know of near my house close at odd non-15-increments time. One closes at 9:40 PM on Sundays, and one closes at 1:40 AM.

There is also the issue of holiday hours , such as stores closing early on thanksgiving day, for example, so you need to have calendar-based override.

Perhaps what can be done is a date/time open, date-time close, such as this:

businessID  | datetime              | type
==========================================
        1     10/1/2008 10:30:00 AM    1
        1     10/1/2008 02:45:00 PM    0
        1     10/1/2008 05:15:00 PM    1
        1     10/2/2008 02:00:00 AM    0
        1     10/2/2008 10:30:00 AM    1

etc. (type: 1 being open and 0 closed)

And have all the days in the coming 1 or two years precalculated 1-2 years in advance. Note that you would only have 3 columns: int, date/time/bit so the data consumption should be minimal.

This will also allow you to modify specific dates for odd hours for special days, as they become known.

It also takes care of crossing over midnight, as well as 12/24 hour conversions.

It is also timezone agnostic. If you store start time and duration, when you calculate the end time, is your machine going to give you the TZ adjusted time? Is that what you want? More code.

as far as querying for open-closed status: query the date-time in question,

select top 1 type from thehours where datetimefield<=somedatetime and businessID = somebusinessid order by datetime desc

then look at "type". if one, it's open, if 0, it's closed.

PS: I was in retail for 10 years. So I am familiar with the small business crazy-hours problems.

Answer 9

How about something like this:

Store Hours Table

Business_id (int)
Start_Time (time)
End_Time (time)
Condition varchar/string
Open bit

'Condition' is a lambda expression (text for a 'where' clause). Build the query dynamically. So for a particular business you select all of the open/close times

Let Query1 = select count(open) from store_hours where @t between start_time and end_time and open  = true and business_id = @id and (.. dynamically built expression)

Let Query2 = select count(closed) from store_hours where @t between start_time and end_time and open = false and business_id = @id and (.. dynamically built expression)

So end the end you want something like:

select cast(Query1 as bit) & ~cast(Query2 as bit)

If the result of the last query is 1 then the store is open at time t, otherwise it is closed.

Now you just need a friendly interface that can generate your where clauses (lambda expressions) for you.

The only other corner case that I can think of is what happens if a store is open from say 7am to 2am on one date but closes at 11pm on the following date. Your system should be able to handle that as well by smartly splitting up the times between the two days.