How will you store and request opening hours?

Question

We are creating an application that stores "hours of operation" for different enterprises. What is the easiest way to present this data so that you can easily check if an item is open?

Some options:

• Highlight the blocks (every 15 minutes) that you can mark "open / closed". The check includes viewing if the "open" bit is set to the right time (a bit like the train schedule).
• Saving a list of time ranges (11:00 to 21:00, 5-7pm, etc.) and checking whether the current time falls in any given range (this is what our brain does when analyzing the lines above).

Does anyone have any experience storing and requesting schedule information and any tips?

(There are all sorts of crazy corner cases like "closed on the first Tuesday of the month," but we'll leave it the next day).

Answer 1

store each continuous block of time as a start time and duration; this makes it easy to check when the clock crosses the date

if you are sure that the opening hours will never cross the date borders (i.e. there will never be a sale from open to full or 72-hour marathon and others), then the start / end time will be sufficient

Answer 2

The most flexible solution may be to use a bitrate approach. There are 168 hours a week, so there are 672 15-minute periods. This is just 84 bytes of space, which should be acceptable.

Answer 3

I would use the table as follows:

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 from 5 to 6, but shorter hours on Sunday.

An if if open query 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 keep edges with edges, that is, a total of 365 records, one per day ... it really isn’t so much in the great scheme of things, put the index in the column of the day and businessId.

Remember to save the time zone of the business in a separate table (normalize!) And perform the conversion between your time and this before doing these comparisons.

Answer 4

I think that I personally will go to the beginning + end time, as this will make everything more flexible. Good question: what is the chance that the block size will change at some point? Then choose the solution that best suits your situation (if it can change, I would choose a time frame).

You can save them as time intervals and use segments in your application. This way you have easy input using blocks, while maintaining flexibility in your data warehouse.

Answer 5

To add to what Johnathan Holland said, I would have made several entries on the same day.

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

Why? many restaurants and some enterprises, and many enterprises all over the world dine on a day of rest. In addition, many restaurants (2 that I know near my house are closed with an odd time of not 15 times. One closes at 9:40 on Sundays, and one closes at 1:40 in the morning.

There is also the issue of holidays, such as closing stores at the beginning of Thanksgiving, so you need to redefine the calendar.

Perhaps what can be done is opening a date / time, closing a date and time, for example:

 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 open and 0 closed)

And let all the days in the next 1 or two years are tentatively designed for 1-2 years in advance. Please note that you will only have 3 columns: int, date / time / bit, so data consumption should be minimal.

It will also allow you to change specific dates for odd hours on special days when they become known.

He also takes care of going through midnight, as well as conversions within 12/24 hours.

It also does not depend on the time zone. If you save the start time and duration when you calculate the end time, will your machine give you TZ adjusted time? Is this what you want? More code.

to request an open close status: request the date in question,

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

then look at the "type". if it is open, if 0, then it is closed.

PS: I have been in retail for 10 years. Therefore, I am familiar with the problems of crazy small business hours.

Answer 6

Well, I will throw it at what it costs.

I need to handle a lot of things.

• Fast / Running Request
• Any time increments, 9:01 pm, 12:14, etc.
• International (?) - I’m not sure that this is a problem even with time zones, at least in my case, but someone more knowledgeable here does not hesitate to call
• Open - close the next day (open at noon, close at 2:00 in the morning).
• Several time slots / days
• Ability to redefine certain days (holidays, regardless)
• Ability to re-enable overrides
• The ability to request at any time and start a business (now, in the future, in the past)
• The ability to easily exclude the results of a business closure in the near future (the filter closes in 30 minutes, you do not want your users to show this guy 5 minutes before closing in the food / drink industry).

I like the many approaches presented, and I borrow some of them. On my website, a project, no matter what I need to consider, I can have millions of businesses, and some of the approaches here don't seem to work well for me personally.

Here is what I propose for the algorithm and structure.

We must make some specific assumptions, around the world, anywhere and anytime: There are 7 days a week. There are 1440 minutes in one day. There are a finite number of permutations of open / closed minutes that are possible.

Not specific, but worthy assumptions: Many open / closed minute permutations will be distributed between enterprises, which will lead to the actual storage of all permutations. There was a time in my life, I could easily calculate the actual possible combinations of this approach, but if someone could help / think that this would be useful, that would be great.

I suggest 3 tables: Before you stop reading, consider in the real world 2 of these tables will be a fairly neat cache. This approach will not be for everyone either because of the complex complexity of the code that is required to interpret the user interface for the data model, and, if necessary, vice versa. Your mileage and needs may vary. This is an attempt at a reasonable "enterprise level", no matter what it means.

Opening hours table

ID | OPEN (minute of the day) | CLOSE (minute of the day)

---

1 | 360 | 1020 (example: from 9:00 to 17:00)

2 | 365 | 1021 (example: extreme case 9:05 AM - 5:01 PM (weirdos))

and etc.

HoursOfOperations does not care about which days, just open, close and uniqueness. In combination with open / closed, there can be only one entry. Now, depending on your environment, this entire table may be cached or may be cached during the current hour of the day, etc. In any case, you do not need to query this table for each operation. Depending on your storage solution, I render each column in this table indexed for performance. Over time, this table probably has an exponentially inverse probability of INSERT (s). In fact, when using this table, there should mainly be an Operational Operation (RAM).

Business2HoursMap

Note. In my example, I save "Day" as the field / column of the bit flag. This has a lot to do with my needs and the promotion of LINQ / Flags Enums in C #. There is nothing stopping you from expanding it to 7 bit fields. Both approaches should be relatively similar in both storage logic and query.

Another note. I do not enter into the semantics argument “each table needs a PC identifier column”, please find another forum for this.

BusinessID | HoursID | Day (or, if you prefer to divide by: BIT Monday, BIT Tuesday, ...)

---

1 | 1 | 1111111 (this business is open 9-5 every day of the week)

2 | 2 | 1111110 (this business is open 9:05 - 5:01 M-Sat (Monday = day 1)

The reason it’s easy to get a request is because we can always easily determine the MOTD (minute of the day) we encountered. If I want to know what will be open at 17:00, I will capture all the HoursOfOperations IDS WHERE Close> = 1020. If I am not looking for a time range, Open becomes insignificant. If you don't want businesses to close in the next half hour, just adjust your inbound time accordingly (find 5:30 PM (1050), not 5:00 PM (1020)). The second request, of course, was to "give me the whole thing with HoursID IN (1, 2, 3, 4, 5), etc. This should probably raise the red flag, since there are limitations to this approach. However, if anyone this might answer the question about the actual permutations above, we can pull out the red flag, consider that we only need the possible permutations on either side of the equation at a time, both open and closed.

Given that we have the first cache table, this is a quick operation. The second operation queries this potentially large table of rows, but we are looking for very small (SMALLINT) reliable indexed columns.

Now you can see the complexity on the code side of things. I am targeting most of the bars in my specific project, so it will be very safe to assume that I will have a significant number of businesses with hours such as "11:00 - 2:00 (the next day)." It really will be 2 entries in the HoursOfOperations table, as well as in the Business2HoursMap table. For instance. The bar, which is open from 11:00 to 02:00, will have 2 links to the HoursOfOperations 660-1440 table (11:00 - midnight) and 0 - 120 (midnight - 2:00 in the morning). These links will be reflected in actual days in the Business2HoursMap table as 2 entries in our simplified case, 1 entry = all days Hours reference # 1, another link to all days # 2. I hope this makes sense, it was a long day.

Override on special days / holidays / whatever. Overrides are inherently based on a date, not a day of the week. I think it is here that some of the approaches try to stick a round pin into a square hole. We need another table.

HoursID | BusinessID | Day | Month | Year

1 | 2 | 1 | 1 | Null

This, of course, can become more complicated if you need something like "every second Tuesday, this company fishes for 4 hours." However, what this will allow us to do quite easily allows 1 - to redefine, 2 - reasonable repeated overrides. EG. if the year is IS NULL, then every year on New Year's Eve this bar is a weirdo open from 9:00 to 17:00 in accordance with our data examples above. That is - If the year was set, this is only for 2013. If the month is zero, then every first day of the month. Again, this will not process each planning scenario with only NULL columns, but theoretically you can process almost everything by relying on a long sequence of absolute dates if necessary.

Again, I will cache this table for a day. I just can't really see the rows of this table in a one-day snapshot, which is very large, at least for my needs. I would check this table first, as it’s good to override and save the query for the much larger Business2HoursMap table on the storage side.

An interesting problem. I am really surprised that this is the first time I really needed to think this through. As always, they are very interested in different views, approaches or shortcomings in my approach.

Answer 7

Segment blocks are better, just make sure you give the user an easy way to set them. Clicking and dragging is good.

Any other system (such as ranges) will be very annoying when you cross the border of midnight.

As for how you store them, C ++ bits will probably be better. In most other languages, an array may be better (a lot of lost space, but it will work faster and easier to understand).

Answer 8

I would think a little about these extreme cases, because they are going to tell you if you have a basic configuration plus an overlay or a full static repository of opening time or something else.

There are so many exceptions - and on a regular basis (for example, snowy days, irregular holidays such as Easter, Good Friday), that if this is expected to be a reliable idea of ​​reality (as opposed to a good guess), you will appeal to him pretty quickly in architecture.

Answer 9

How about something like this:

Watch Shop Table

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

A "condition" is a lambda expression (the text for the where clause). Build a query dynamically. Therefore, for a particular business, you choose all the time of opening / closing

 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 in the end you want something like:

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

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

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

The only other argument in the corner that I can come up with is what happens if the store is open from 7am to 2am on one date, but closes at 11:00 on the next date. Your system should be able to handle this, as well as intelligently split the time between two days.

Answer 10

Of course, there is no need to save memory here, but perhaps there is a need for clean and understandable code. "Bit twisting" - no, IMHO, the way.

We need a container container that contains any number of unique elements and can quickly and easily determine whether an element is a member or not. Installation requires caution, but in normal use, one line of easy-to-understand code determines whether you are open or closed

Concept: Assign an index number every 15-minute block, starting, for example, on midnight Sunday.

Initialize: Insert an index number into the set every 15 minutes when you are open. (Assuming you are open less hours than you are closed.)

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