MySQL 存储工作时间和高效查询的最佳方式

Best way to store working hours and query it efficiently

提问by khelll

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.

我打算存储商店的工作时间。我想知道什么可能是工作时间领域的最佳建模，以便我可以非常有效地获得当前开放/关闭的商店列表。

回答by Robert Gowland

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

• 商店 - 整数
• 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:

例如，我假设每个商店在国定假日期间减少了工作时间，或者工厂停工，因此您还需要存储一些覆盖记录：

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

• 商店 - 整数
• 覆盖开始日期 - 日期
• 覆盖结束日期 - 日期
• 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.

如果有任何记录返回，这些商店会交替营业或关闭。

There may be some nice SQL-fu you can do here, but this gives you the basics.

您可以在这里执行一些不错的 SQL-fu，但这为您提供了基础知识。

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.

至于效率，从某种意义上说，它是有效的，您只需调用一次 MySQL，如果它是跨网络的，这通常是一个瓶颈。您必须测试并查看这是否符合您的规格。如果没有，您可能会使用一些索引。

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)

回答by eumiro

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)

• shop_id - INTEGER（或商店的任何唯一标识符）
• week_day - 整数（0=周一 - 6=周日）
• opens_at - TIME（使用您当地的时区）
• closes_at - TIME（使用您当地的时区）

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

为由 标识的商店制作一张表格shop_id，然后插入营业时间，即：

• 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
• ...

• 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