I want to find a way to store a businesses hours of operation in such a manner that it can be queried easily on the front-end. For example, suppose I want a filter that will filter for businesses that are open at the current day and time, and another filter that will filter for businesses that are open tomorrow. Does anybody have a good way to do this?
The best I can come up with is to use a JSON array of “day/time blocks” for the hours that the business is open. What seems to be tricky is dealing with businesses that are closed for certain times during the day, such as a business that’s open form 09:00AM until 11:00AM, and then closed between 11:01AM and 12:00PM, then re-opens from 12:00PM until 5:00PM.
You realize that JSON isnt a Database, right? looks up at category for a moment
Right, anyway. Moving on.
I can tell you how I would do it in a database, store a row in a a table containing the store’s ID, day-of-week number, an opening time in seconds (where 0 is midnight and 86399 is 11:59:59 PM), a closing time in seconds, and then doing a couple of BETWEEN queries to answer questions, but there will inevitably be problematic situations here.
Lol, yes I realize JSON isn’t a database. What I meant was to store a json array as one of the keys in the database.
One problem I’d like to be able to address is being able to handle businesses that close for a certain period during the day. For example, many restaurants close after the lunch rush, then re-open before dinner. If I store one “set” of open/close times for each day, I can’t account for that.
With regards to timezones, it would make sense (to me, anyway) if the business’ hours in the database were based on their local timezone. The frontend could handle the descrepancy between the user’s timezone and that of the business location.
Holidays will likely be a cluster no matter what, especially since many businesses don’t end up knowing their holiday hours until close to the date of the holiday (and they probably aren’t going to update them).
So… the frontend is going to change your database queries to find and figure out the timezone of the business?
Keep in mind that you’ve said:
So… the front end cant give you a value in a specific time zone, because you’re searching all businesses for a timespan… also, what’s the definition of “tomorrow”? the Business’ tomorrow? the user’s?
Time is a horror, no matter how you do it. The edge cases are endless.
But, lets put that aside for the moment, and assume we live in a happy world where everyone uses UTC (Yaknow, the “Coordinated” part of UTC.) and defines tomorrow as the span from the next midnight UTC to 23:59:59 UTC.
Lets look at a business that has complicated hours, like you say; my fictional business:
Is closed on Sunday and Monday.
Is open on Tuesday through Friday from 8 AM to 9 PM.
Is open on Saturday from 9AM to 11 AM, and from Noon to 5PM.
(My business sounds like a DMV. Anyway.)
My SQL Database representation of Business #9931:
bid
day
open
close
9931
2
28800
75599
9931
3
28800
75599
9931
4
28800
75599
9931
5
28800
75599
9931
6
32400
39599
9931
6
43200
61199
(If you want, you can store the times as actual TIMEs, but… integers works for me :P)
Translate the current time to the number of seconds (Hint: The Timestamp Modulo 86400 [24*60*60]), and get the current dayofweek (Hint: There’s a function for that. Also Sunday = 0.) SELECT bid FROM times WHERE day = [dayOfWeek] AND [number of seconds] BETWEEN open AND close
Anything that comes back is open right now.
I prefaced my example with “assume we live in a happy world where everyone uses UTC”. UTC has no DST. Dates and Times will ALWAYS have problems unless you define a constant environment.
That’s not true. As long as you convert an inputed date directly to UTC, internally only work with UTC and convert it back to local TZ only on output, you are always fine
Why would the frontend client need to change the actual database queries at all?
While I’ve never done it, I’m certain there must be a way to “translate” on the frontend then query the actual values in the database based on the end-user’s local timezone.
IE if the local user is Pacific Time, and it’s 1:00PM, query for businesses that are open after 2100UTC, if that’s what’s stored in the database, (since Pacific is UTC-8:00), so the query is still sent based on the actual database values, but different users in different timezones will send different queries to represent the same “time,” IE a user in Central Timezone will query for 1800UTC to represent 1:00PM in their time.
Does your today end at 11:59:59 UTC? Or the end user’s 11:59:59? (this is the whole… “if everyone lived in a world of UTC” part. We don’t. Which is why it all falls apart)
It doesn’t matter what time zone the end-user thinks they’re using, if all of the queries are actually sent using UTC, right? The end-user sees the time as 11:59PM PST on Thursday, he’s actually going to query businesses that are open after 07:59AM UTC on Friday.
Just like if I denominate the price of my products in dollars, but you make an offer in cents. We can still do business because ultimately we’re talking about the same currency, just denominated differently, no?
Imagine you live next to a timezone border and search for a shop which has open until 20:00PM. If you show him the shops in the next timezone which have open till 20:00PM their time, and the user drives to it but is one hour too late because in his timezone is one hour earlier?
Yeah, this is the eternal problem of timezones - its always something different somewhere.
Ah so now the query’s changing to span multiple days, huh?
cause you cant say day = X as a condition if you need to look at day = X and X+1!
or is it X-1? could be, if youre ahead of UTC… oh and we used a 0-7 day counter, so its not just + or - 1, because you have to account for looping back around on the ends of the week and…
(you starting to see why dates and times are a hassle yet? )
(this is the whole… “if everyone lived in a world of UTC” part. We don’t. Which is why it all falls apart)