php—一种存储开放时间的方法,用于确定一个地方现在是否开放

h7appiyu  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(360)

我希望能够存储多家餐厅的营业时间,并确定一家店现在是否营业,但我一直在研究如何处理午夜后关门的地方。
以下是一个餐厅营业时间的例子:

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

我不知道我会如何处理滚动时间,例如,以确定餐厅是否将开放45分钟从现在起在周六晚上11:55。
以下是餐厅营业时间的另一个例子:

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小时营业,所以我也需要处理这个案子。
餐厅可以全天候营业:

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

餐厅每天可以有0、1、2个或更多时段:

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个周期是有意义的,但除此之外,我不确定如何做到这一点。

pu82cl6c

pu82cl6c1#

我想我可能会这么想:

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。我想下面会告诉我们餐厅目前营业的时间(考虑到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来表达这一点,或者在应用程序代码中处理位解码可能更聪明,

相关问题