gistfile1.sql

/*
This query returns the id's of stores which are open right now.

The date field for an opening hour can be either an explicit date or a weekday, an explicit date has higher priority than a weekday, and thus overrides a weekday.

A store is considered closed the whole day if there is an explicit date set (not a weekday) and closed=1 - or - no opening_hour is registered for the store on the day

Example data:
store_id      date        open_time     close_time    closed     dayandnight     closes_after_midnight
----------    --------    ----------    ----------    -------    ------------    ----------------------
113243        monday      06:00:00      22:00:00      0          0               0
113243        tuesday     06:00:00      22:00:00      0          0               0
113243        wednesday   06:00:00      22:00:00      0          0               0
113243        friday      06:00:00      02:00:00      0          0               1
113243        saturday    00:01:00      23:59:00      0          1               0
113243        2012-03-12  00:00:00      00:00:00      1          0               0

Also note:
If the current time is before 04:30 (am), the date is set to yesterday's date
*/

SELECT store_id FROM opening_hours WHERE date='2012-03-30' /* First check if we can find any opening hours for todays date */
	AND
	(
			dayandnight=1 /* Store is open day and night? */
			OR
	        (
	        	/* If store closes after midnight, subtract 24 hours from open_time before checking if stores is based based on hours */
	        	(closes_after_midnight = 1 AND (SUBTIME(open_time, '24:00:00') < CURTIME() AND close_time > CURTIME()))
	        	OR
	        	/* If store closes before midnight, just check if current time is within open and close time */
	        	(closes_after_midnight = 0 AND (open_time < CURTIME() AND close_time > CURTIME()))
	        )
	) AND closed=0 /* Do not select stores which are explicitly closed */
UNION
SELECT store_id FROM opening_hours WHERE (date='friday') /* Then check if we can find any opening hours for todays weekday */
	AND
	(
	        dayandnight=1 /* Store is open day and night? */
			OR
	        (
	        	/* If store closes after midnight, subtract 24 hours from open_time before checking if stores is based based on hours */
	        	(closes_after_midnight = 1 AND (SUBTIME(open_time, '24:00:00') < CURTIME() AND close_time > CURTIME()))
	        	OR
	        	/* If store closes before midnight, just check if current time is within open and close time */
	        	(closes_after_midnight = 0 AND (open_time < CURTIME() AND close_time > CURTIME()))
	        )
	)
	AND store_id NOT IN ( /* DO NOT select weekday stores if we already got a match from the date. NB! This query also includes explicitly closed stores */
		SELECT store_id FROM opening_hours WHERE date='2012-03-30'
		AND
		(
		        dayandnight=1 /* Store is open day and night? */
				OR
	        	(
		        	/* If store closes after midnight, subtract 24 hours from open_time before checking if stores is based based on hours */
		        	(closes_after_midnight = 1 AND (SUBTIME(open_time, '24:00:00') < CURTIME() AND close_time > CURTIME()))
		        	OR
		        	/* If store closes before midnight, just check if current time is within open and close time */
		        	(closes_after_midnight = 0 AND (open_time < CURTIME() AND close_time > CURTIME()))
	        	)
		)
	)

Hi, Mads asked that we might have a look at your SQL.

For performance, it is better that the table's date field for date is split your into two parts. The actual date (as a date datatype), and the day of the week (dateday, as an small int). The day of the week can represented as a bitmask, (i.e. the bit stream 0010010 represent wednesday and saturday open, and then use bitwise AND to check for dates). These changes allow the database to go from a string search to integer search.

Depending on how you query the date. You should place an index on the structure. I'll recommend one for (date, dateday), and (dateday). The reason a single index for date is not issued, is because the database is able to utilize the index with only the first part of the key. However, you might want to try to add the index later and see if the query optimizer find a bettet query plan.

If your app is being used in multiple timezones, you might already now build in some simple timezone support and store the data in GMT format, and in client-space add the require timezone shift.

Further optimizations of the SQL is possible, but it takes a little more time. Look into it if you get performance problems later.

Hi Mathias, Thank you so much for taking your time to look through this. This gave me a lot of useful information for further development of the data structure. I have however decided now to migrate from a relational MySQL database to a NoSQL database. This should make me able to have a much cleaner and scalable data model suited for geospatial searches. I have decided to make two separate fields for dates and weekdays as per your suggestions, i will however not store the weekdays as a bit mask, as i think that it would only complicate the search operation more. Fortunately the query speed is currently not a bottleneck, but i am just unhappy with the previous data structure because the SELECT queries deemed to be very complicated for finding nearby stores. 2012/4/10 Matias Bjørling < reply@reply.github.com

Hi, Mads asked that we might have a look at your SQL. For performance, it is better that the table's date field for date is split your into two parts. The actual date (as a date datatype), and the day of the week (dateday, as an small int). The day of the week can represented as a bitmask, (i.e. the bit stream 0010010 represent wednesday and saturday open, and then use bitwise AND to check for dates). These changes allow the database to go from a string search to integer search. Depending on how you query the date. You should place an index on the structure. I'll recommend one for (date, dateday), and (dateday). The reason a single index for date is not issued, is because the database is able to utilize the index with only the first part of the key. However, you might want to try to add the index later and see if the query optimizer find a bettet query plan. If your app is being used in multiple timezones, you might already now build in some simple timezone support and store the data in GMT format, and in client-space add the require timezone shift. Further optimizations of the SQL is possible, but it takes a little more time. Look into it if you get performance problems later. --- Reply to this email directly or view it on GitHub: https://gist.github.com/2245997

## Med venlig hilsen _Mathias Hansen_ www.dotsquare.dk www.matisen.dk