Modeling business hours for 'open now' queries


#1

Suppose i have a set of users that are businesses and these businesses can have business hours in their respective timezones (ie America/Los_Angeles...). For each of these businesses, they have a set of items, possibly in the hundreds.

I want to perform queries like, "grab me 50 items among all businesses in my area that are open now". I'm not sure the best way to model this considering I'll need to replicate the business hours across the user and all of its items.

My best guess for the most efficient queries would be to model a user/its items like this:

/users/_doc/1
{
	name: 'bus 1',
	uid: 'alpha',
	...
	hours: {
		0: true,
		49: true
		,...
	}
}

/items/_doc/100
{
	name: 'item name',
	key: 'bravo',
	...
	owner_data: {
		name: 'bus 1',
		uid: 'alpha',
		...
		hours: {
			0: true,
			49: true
			,...
		}
	}
}

hours.0 = true indicates this business is open 00:00->00:30 UTC Sunday.
hours.49 = true indicates this business is open 00:30->01:00 UTC Monday.

This means there are a maximum of 336 (7 days a week * 48 half hours) fields under the hours object.

The advantage to this is I can get the current half hour (say 49) and query those items where owner_data.hours.49 === true. Another advantage is this allows the users to have multiple open segments, ie '12:00-> 18:00, 20:00 -> 22:00`.

Finally, I'm not sure if the following is a disadvantage, and let me know if it isn't. This is a lot of data to replicate for just weekly business hours. An hours object could be 1kb, and in elastic could be much more when placed into the index.

Are there better alternatives? I've thought of the possibility of not storing hours data in the documents for items at all and doing a two stage query; 1st query the users that are open and then query items whose owner_data.uid === user1openuid || ownerdata.uid === user2openuid ||... I imagine i could have hundreds of uids id have to OR together for each query which could be expensive, IDK.

Also if my original approach is plausible, what would the mapping look like for the hours object? In my opinion, that's a lot of fields for a mapping.


(Mark Walkom) #2

Try https://www.elastic.co/guide/en/elasticsearch/reference/6.2/range.html instead, it'll be a lot more optimal.


#3

Ok i will try this and post back my results.


(system) #4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.