I'm planning to store working hours for shops. I'm wondering what might be the best modeling for the working hours field so that I can get a list of open/closed shops at the current moment in very efficient way.
Storing time series data. Time series data is best stored in a time series database (TSDB) built specifically for handling metrics and events that are time-stamped. This is because time series data is often ingested in massive volumes that require a purpose-built database designed to handle that scale.
hh is two digits, ranging from 0 to 23, that represent the hour. mm is two digits, ranging from 0 to 59, that represent the minute. ss is two digits, ranging from 0 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds.
To store normal operation hours, you would need to store a number of records containing:
I assume for example that each shop has reduced hours during national holidays, or has plant shutdowns, so you would also need to store some override records:
To find open shops is trivial, but you also need to check if there are override hours:
SELECT Shop FROM OverrideHours WHERE OverrideStartDate <= NOW() AND OverrideEndDate >= NOW() AND DayOfWeek = WEEKDAY(NOW())
If there are any record returned, those shops have alternate hours or are closed.
There may be some nice SQL-fu you can do here, but this gives you the basics.
EDIT
I haven't tested this, but this should get you close:
SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND NOW() BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(NOW()) AND ((Override.Shop IS NULL AND TIME(NOW()) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(NOW()) BETWEEN Override.AltOpenTime AND Override.AltCloseTime))
EDIT
As for efficiency, it is efficient in the sense that you only have to make one call to MySQL which is often a bottleneck if it is across a network. You'll have to test and see whether this performs to your specifications. If not, you may be to play with some indices.
EDIT
Testing. Not complete testing, but some.
mysql> select * from Normal; +------+-----------+----------+-----------+ | Shop | DayOfWeek | OpenTime | CloseTime | +------+-----------+----------+-----------+ | 1 | 1 | 09:00:00 | 17:00:00 | | 1 | 5 | 09:00:00 | 16:00:00 | | 2 | 1 | 09:00:00 | 17:00:00 | | 2 | 5 | 09:00:00 | 17:00:00 | +------+-----------+----------+-----------+ 4 rows in set (0.01 sec) mysql> select * from Override; +------+-------------------+-----------------+-----------+-------------+--------------+--------+ | Shop | OverrideStartDate | OverrideEndDate | DayOfWeek | AltOpenTime | AltCloseTime | Closed | +------+-------------------+-----------------+-----------+-------------+--------------+--------+ | 2 | 2010-12-01 | 2010-12-31 | 1 | 09:00:00 | 18:00:00 | 0 | | 2 | 2010-12-01 | 2010-12-31 | 5 | 09:00:00 | 18:00:00 | 0 | | 1 | 2010-12-01 | 2010-12-31 | 1 | 09:00:00 | 17:00:00 | 1 | +------+-------------------+-----------------+-----------+-------------+--------------+--------+ 3 rows in set (0.00 sec) mysql> SET @whenever = TIMESTAMP('2010-11-23 16:05'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT WEEKDAY(@whenever); +--------------------+ | WEEKDAY(@whenever) | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime)); +------+ | Shop | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) mysql> SET @whenever = TIMESTAMP('2010-11-23 17:05'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime)); Empty set (0.01 sec) mysql> SET @whenever = TIMESTAMP('2010-12-25 16:05'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime)); +------+ | Shop | +------+ | 2 | +------+ 1 row in set (0.00 sec) mysql> SET @whenever = TIMESTAMP('2010-11-23 17:05'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT WEEKDAY(@whenever); +--------------------+ | WEEKDAY(@whenever) | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime)); Empty set (0.00 sec)
Let's consider all opening hours are the same every week. So what about following table:
Make a table for shops identified by shop_id
and then insert the opening hours, i.e:
and then SELECT:
SELECT shop_id FROM opening_hours WHERE WEEKDAY(NOW()) = week_day AND TIME(NOW()) BETWEEN opens_at AND closes_at
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With