php - 一种存储开放时间以确定某个地方现在是否开放的方法

Question

我希望能够存储多家餐厅的营业时间并确定一家商店现在是否营业，但我对如何处理午夜后关门的地方感到困惑。

Here's an example of a restaurant's opening hours:

Monday: 6:00AM-10:00PM
Tuesday: 6:00AM-10:00PM
Wednesday: 6:00AM-10:00PM
Thursday: 6:00AM-10:00PM
Friday: 6:00AM-11:59PM
Saturday: 0:00AM-3:00AM, 6:00AM-11:59PM
Sunday: 0:00AM-3:00AM, 6:00AM-10:00PM

我不确定应该使用哪种类型的模式来表示这一点，以便我可以确定特定餐厅现在是否营业。

设置架构后，我的目标是能够确定任何特定餐厅的以下内容:

• 餐厅是否会在 15 分钟后营业
• 餐厅是否会在 45 分钟后关门

因此，如果当前时间是餐厅开门前 15 分钟，则餐厅应被视为营业，如果当前时间是餐厅关门前 45 分钟，则餐厅应被视为已关闭，两者之间的任何时间都应被视为营业，考虑到午夜的翻转。

下面是一些预期的输入和结果:

Restaurant 1
Monday: 6:00AM-10:00PM
Tuesday: 6:00AM-10:00PM
Wednesday: 6:00AM-10:00PM
Thursday: 6:00AM-10:00PM
Friday: 6:00AM-11:59PM
Saturday: 0:00AM-3:00AM, 6:00AM-11:59PM
Sunday: 0:00AM-3:00AM, 6:00AM-10:00PM

Current day/time -> Result
Monday 12:00PM -> Open
Monday 2:00AM -> Closed
Monday 5:45AM -> Open
Monday 9:30PM -> Closed
Saturday 4:00AM -> Closed
Saturday 11:55PM -> Open
Sunday 2:00AM -> Open
Sunday 2:25AM -> Closed

我不确定我将如何处理翻转时间，例如确定餐厅是否会在周六晚上 11:55 从现在起 45 分钟后营业。

Here's another example of a restaurant's opening hours:

Monday: 10:00AM-10:00PM
Tuesday: 10:00AM-10:00PM
Wednesday: 10:00AM-10:00PM
Thursday: 10:00AM-10:00PM
Friday: 10:00AM-11:59PM
Saturday: 0:00AM-11:59PM
Sunday: 0:00AM-9:00PM

周六这家店是24小时营业的，所以我也需要能够处理这个案子。

A restaurant could be open 24/7:

Monday: 0:00AM-11:59PM
Tuesday: 0:00AM-11:59PM
Wednesday: 0:00AM-11:59PM
Thursday: 0:00AM-11:59PM
Friday: 0:00AM-11:59PM
Saturday: 0:00AM-11:59PM
Sunday: 0:00AM-11:59PM

A restaurant could have 0, 1, 2, or more periods per day:

Monday: CLOSED
Tuesday: CLOSED
Wednesday: 10:00AM-2:00PM, 5:00PM-10:00PM
Thursday: 10:00AM-2:00PM, 5:00PM-10:00PM
Friday: 10:00AM-2:00PM, 5:00PM-11:59PM
Saturday: 0:00AM-3:00AM, 10:00AM-2:00PM, 5:00PM-11:59PM
Sunday: 0:00AM-3:00AM, 10:00AM-2:00PM, 5:00PM-9:00PM

所以我的目标是找到给定餐厅及其营业时间的当前时间是否介于:

• 开场前 15 分钟，以及
• 关门前 45 分钟，
• 当天的任何时段

并找到一种存储信息的好方法，以便可以确定多家餐厅的信息。

因为每天可能有 0 个或多个时间段，所以我认为每行存储 1 个时间段是有意义的，但除此之外我不确定该怎么做。

Answer 1

我想我可能会想这样存储它:

bit_code opens    closes
69       06:00:00 22:00:00 
48       06:00:00 03:00:00

我用数字表示了星期几，如下所示:

Monday    -  1
Tuesday   -  2
Wednesday -  4
Thursday  -  8
Friday    - 16
Saturday  - 32
Sunday    - 64

所以，星期五 + 星期六 = 48

编辑:

考虑以下示例:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(weekday_code INT NOT NULL
,opens TIME NOT NULL
,closes TIME NOT NULL
);

INSERT INTO my_table VALUES
(69,'06:00:00','22:00:00'),
(48,'06:00:00','03:00:00');

现在是 23:30:00。我认为以下内容会告诉我们餐厅目前在一周中的哪几天营业(并考虑 15 分钟和 45 分钟的规则)...

SELECT *  
  FROM my_table 
 WHERE CAST('23:30:00' AS TIME) >= opens - INTERVAL 15 MINUTE
   AND CAST('23:30:00' AS TIME) <= (CASE WHEN closes < opens THEN closes + INTERVAL 24 HOUR ELSE closes END) - INTERVAL 45 MINUTE; 

+--------------+----------+----------+
| weekday_code | opens    | closes   |
+--------------+----------+----------+
|           48 | 06:00:00 | 03:00:00 |
+--------------+----------+----------+ 

我们可以在应用程序代码或 SQL 中引入其他技巧来破译“48”指的是一周中的哪几天。同样，我们不必以这种方式存储星期几；它看起来非常紧凑，特别是如果有很多餐馆(营业时间相当一致)。

无论如何，如果采用这种方法......只是为了好玩/完整......

SELECT a.x 
FROM 
   ( SELECT  1 x UNION
     SELECT  2 UNION
     SELECT  4 UNION
     SELECT  8 UNION
     SELECT 16 UNION
     SELECT 32 UNION
     SELECT 64 
   ) a
JOIN my_table b
  ON a.x = ( a.x & b.weekday_code) 
 AND CAST('23:30:00' AS TIME) >= b.opens - INTERVAL 15 MINUTE
 AND CAST('23:30:00' AS TIME) <= (CASE WHEN b.closes < b.opens THEN b.closes + INTERVAL 24 HOUR ELSE b.closes END) - INTERVAL 45 MINUTE; 

 +----+
 | x  |
 +----+
 | 16 |
 | 32 |
 +----+

由此我们可以推断，如果今天是星期五或星期六，则餐厅营业。

请注意，在 SQL 中可能有更优雅的表达方式 - 或者在应用程序代码中处理位解码可能更智能，