最好的方式来存储工作时间和有效地查询 [英] Best way to store working hours and query it efficiently

查看：101
本文介绍了最好的方式来存储工作时间和有效地查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我打算为商店存储工作时间。我想知道什么可能是工作时间字段的最好的建模，以便我可以得到一个开放/关闭商店在当前时刻以非常有效的方式的列表。

解决方案

要存储正常操作时间，您需要存储包含以下内容的若干记录：




  • Shop - INTEGER

  • DayOfWeek - INTEGER（0-6）

  • OpenTime - TIME

  • 关闭时间 - TIME



我假设每个商店在国定假日期间减少工作时间或关闭工厂，因此您还需要存储一些覆盖纪录：




  • 购物 - INTEGER

  • OverrideStartDate - DATE

  • OverrideEndDate - DATE

  • DayOfWeek - INTEGER（0-6）

  • AltOpenTime - TIME

  • AltCloseTime - TIME

  • 已关闭 - INTEGER（0，1）



但您还需要检查是否有覆盖时间：

  SELECT Shop 
 FROM OverrideHours 
 WHERE OverrideStartDate< = NOW（）
 AND OverrideEndDate> = NOW（）
 AND DayOfWeek = WEEKDAY（NOW（））

如果有任何记录返回，那些商店有替代时间或已关闭。




$ b

编辑

>我没有测试这个，但这应该让你关闭：

  SELECT Normal.Shop 
 FROM Normal 
 LEFT JOIN Override 
 ON Normal.Shop = Override.Shop 
 AND Normal.DayOfWeek = Override.DayOfWeek 
和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



至于效率，它是有效的，在某种意义上，你只需要调用MySQL，这往往是一个瓶颈，如果它是跨越网络。你必须测试，看看这是否符合你的规格。



测试。未完成测试，但有一些。

  mysql>选择*从正常; 
 + ------ + ----------- + ---------- + ----------- + 
 |商店| 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行（0.01秒）
 
 mysql> select * from Override; 
 + ------ + ------------------- + ----------------- + ----------- + ------------- + -------------- + -------- + 
 |商店| OverrideStartDate | OverrideEndDate | DayOfWeek | AltOpenTime | AltCloseTime |关闭| 
 + ------ + ------------------- + ----------------- + ----------- + ------------- + -------------- + -------- + 
 | 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行（0.00秒）
 
 mysql> SET @whenever = TIMESTAMP（'2010-11-23 16:05'）; 
查询OK，0行受影响（0.00秒）
 
 mysql> SELECT WEEKDAY（@whenever）; 
 + -------------------- + 
 | WEEKDAY（@whenever）| 
 + -------------------- + 
 | 1 | 
 + -------------------- + 
集合中的1行（0.00秒）
 
 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（（覆盖.Shop IS NULL AND TIME（@无论何时）BETWEEN Normal.OpenTime和Normal.CloseTime）OR（Override.Shop IS NOT NULL AND Override.Closed<> 1 AND TIME（@Whenever）BETWEEN Override.AltOpenTime AND Override.AltCloseTime ））; 
 + ------ + 
 |商店| 
 + ------ + 
 | 1 | 
 | 2 | 
 + ------ + 
集合中的2行（0.00秒）
 
 mysql> SET @whenever = TIMESTAMP（'2010-11-23 17:05'）; 
查询OK，0行受影响（0.00秒）
 
 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（（覆盖.Shop IS NULL AND TIME（@无论何时）BETWEEN Normal.OpenTime和Normal.CloseTime）OR（Override.Shop IS NOT NULL AND Override.Closed<> 1 AND TIME（@Whenever）BETWEEN Override.AltOpenTime AND Override.AltCloseTime ））; 
空集（0.01秒）
 
 mysql> SET @whenever = TIMESTAMP（'2010-12-25 16:05'）; 
查询OK，0行受影响（0.00秒）
 
 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（（覆盖.Shop IS NULL AND TIME（@无论何时）BETWEEN Normal.OpenTime和Normal.CloseTime）OR（Override.Shop IS NOT NULL AND Override.Closed<> 1 AND TIME（@Whenever）BETWEEN Override.AltOpenTime AND Override.AltCloseTime ））; 
 + ------ + 
 |商店| 
 + ------ + 
 | 2 | 
 + ------ + 
集合中的1行（0.00秒）
 
 mysql> SET @whenever = TIMESTAMP（'2010-11-23 17:05'）; 
查询OK，0行受影响（0.00秒）
 
 mysql> SELECT WEEKDAY（@whenever）; 
 + -------------------- + 
 | WEEKDAY（@whenever）| 
 + -------------------- + 
 | 1 | 
 + -------------------- + 
集合中的1行（0.00秒）
 
 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（（覆盖.Shop IS NULL AND TIME（@无论何时）BETWEEN Normal.OpenTime和Normal.CloseTime）OR（Override.Shop IS NOT NULL AND Override.Closed<> 1 AND TIME（@Whenever）BETWEEN Override.AltOpenTime AND Override.AltCloseTime ））; 
空集（0.00秒）


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:

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

这篇关于最好的方式来存储工作时间和有效地查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆