开放时间

Question

我正在尝试找出查询以下开放时间的最有效方法:

+---------+----------+----------+-----------+
| openDay | openTime | closeDay | closeTime |
+---------+----------+----------+-----------+
|       0 | 1800     |        1 |  400      |
|       1 | 1800     |        2 |  400      |
|       2 | 1800     |        3 |  400      |
|       3 | 1800     |        4 |  400      |
|       4 | 1800     |        5 |  400      |
|       5 | 1800     |        6 |  400      |
|       6 | 1800     |        0 |  400      |
+---------+----------+----------+-----------+

给一周中的某一天 (0...6) 我想确定一个地方当前是否开放。如果所有地方都在同一天开门和关门，我可以简单地运行如下命令:

SELECT COUNT(*) 
FROM opening_hrs
WHERE (100*HOUR(NOW())+MINUTE(NOW()) >= openTime) 
AND (100*HOUR(NOW())+MINUTE(NOW()) <= closeTime)
AND openDay = 1

但是，如果使用 openDay = 1，该地点在技术上开放时间为 0-400 和 1800-2400。我如何更改查询以说明这些多个时间段？

表格格式是生产表格，因此在这种特定情况下更改格式不起作用(尽管会大大缓解问题)。

Answer 1

如果你不能修改表，那么你可以使用这个查询:

SELECT COUNT(*) 
FROM opening_hrs
WHERE (((100*HOUR(NOW())+MINUTE(NOW()) >= openTime) 
        AND (100*HOUR(NOW())+MINUTE(NOW()) <= closeTime + 2400*(closeDay-openDay)))
       OR 
       ((100*HOUR(NOW())+MINUTE(NOW()) >= openTime - 2400*(closeDay-openDay)) 
        AND (100*HOUR(NOW())+MINUTE(NOW()) <= closeTime )))
AND openDay = 1

---

如果你可以修改表格如下:

+---------+----------+-----------+
|     day | openTime | closeTime |
+---------+----------+-----------+
|       0 | 1800     |  2400     |
|       1 | 1800     |  2400     |
|       2 | 1800     |  2400     |
|       3 | 1800     |  2400     |
|       4 | 1800     |  2400     |
|       5 | 1800     |  2400     |
|       6 | 1800     |  2400     |
|       0 |    0     |   400     |
|       1 |    0     |   400     |
|       2 |    0     |   400     |
|       3 |    0     |   400     |
|       4 |    0     |   400     |
|       5 |    0     |   400     |
|       6 |    0     |   400     |
+---------+----------+-----------+

然后你可以像这样执行查询:

SELECT COUNT(*) 
FROM opening_hrs
WHERE (100*HOUR(NOW())+MINUTE(NOW()) >= openTime) 
AND (100*HOUR(NOW())+MINUTE(NOW()) <= closeTime)
AND day = 1

注意这里不再有openDay和closeDay；只是一个 day 列。另请注意，openTime 始终小于 closeTime，因此查询中的不等式可以起作用。