Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Best way to store working hours and query it efficiently

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.

like image 507
khelll Avatar asked Dec 16 '10 20:12

khelll


People also ask

How do you efficiently store and query time series data?

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.

How do you represent time in a database?

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.


2 Answers

To store normal operation hours, you would need to store a number of records containing:

  • Shop - INTEGER
  • DayOfWeek - INTEGER (0-6)
  • OpenTime - TIME
  • CloseTime - TIME

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:

  • Shop - INTEGER
  • OverrideStartDate - DATE
  • OverrideEndDate - DATE
  • DayOfWeek - INTEGER (0-6)
  • AltOpenTime - TIME
  • AltCloseTime - TIME
  • Closed - INTEGER (0, 1)

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) 
like image 120
Robert Gowland Avatar answered Sep 23 '22 23:09

Robert Gowland


Let's consider all opening hours are the same every week. So what about following table:

  • shop_id - INTEGER (or any unique identifier of the shop)
  • week_day - INTEGER (0=Monday - 6=Sunday)
  • opens_at - TIME (use your local timezone)
  • closes_at - TIME (use your local timezone)

Make a table for shops identified by shop_id and then insert the opening hours, i.e:

  • 1, 0, 8:00, 17:00
  • ...
  • 1, 5, 8:00, 12:00
  • 2, 0, 7:30, 12:30
  • 2, 0, 13:30, 17:30
  • 2, 1, 7:30, 12:30
  • 2, 1, 13:30, 17:30
  • ...

and then SELECT:

SELECT shop_id FROM opening_hours WHERE WEEKDAY(NOW()) = week_day AND TIME(NOW()) BETWEEN opens_at AND closes_at 
like image 25
eumiro Avatar answered Sep 19 '22 23:09

eumiro