存储工作时间和高效查询的最佳方式
我打算存储商店的工作时间.我想知道什么可能是工作时间领域的最佳建模，以便我能够以非常有效的方式获得当前开放/关闭的商店列表.
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.
推荐答案
要存储正常的营业时间，您需要存储一些包含以下内容的记录:
To store normal operation hours, you would need to store a number of records containing:
- 商店 - INTEGER
- DayOfWeek - 整数 (0-6)
- 开放时间 - 时间
- 关闭时间 - 时间
例如，我假设每个商店在国定假日期间减少了营业时间，或者工厂停工，因此您还需要存储一些覆盖记录:
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:
- 商店 - INTEGER
- 覆盖开始日期 - 日期
- 覆盖结束日期 - 日期
- DayOfWeek - 整数 (0-6)
- AltOpenTime - 时间
- AltCloseTime - 时间
- 关闭 - 整数 (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.
您可以在这里执行一些不错的 SQL-fu，但这为您提供了基础知识.
There may be some nice SQL-fu you can do here, but this gives you the basics.
编辑
我还没有测试过这个，但这应该会让你接近:
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))
编辑
至于效率，从某种意义上说，它是高效的，您只需调用一次 MySQL，如果它跨网络，这通常是一个瓶颈.您必须进行测试，看看这是否符合您的规格.如果没有，你可能会玩一些索引.
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.
编辑
测试.不是完整的测试，但有一些.
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)
相关文章