我正在构建一个 api,其中列出了午夜后营业的酒吧列表。我在确定它们当前是打开还是关闭时遇到问题。
例如,假设其中一个酒吧有以下营业时间:
Monday
open time = 20:00
closed time = 05:00
Tuesday
open time = 20:00
closed time = 04:00
如果我想知道酒吧当前是否开放并且当前时间 04:00(星期一到星期二),这将导致当前酒吧关闭,因为它的星期二和关闭时间 = 04:00
但实际结果应该是酒吧营业至05:00(周一至周二)
我有一个 20+ 条形的列表,我需要知道它们是当前打开还是关闭。这可以在 mysql 或 php 中完成吗?我应该如何设置我的数据库表?
我有这个作为我当前的设置
表名 : 营业时间领域:
id int(11)
bar_id int(11)
open_time time
close_time time
day tinyint(1)
表记录:
id: 1
bar_id: 1
open_time: 20:00:00
closed_time: 05:00:00
day: 0
id: 2
bar_id: 1
open_time: 20:00:00
closed_time: 04:00:00
day: 1
此查询适用于不超过午夜的开放日
SELECT `open_time`, `closed_time`, IF(CURTIME() BETWEEN `open_time`
AND `closed_time`,'open','closed') AS `status` FROM `openhours`
WHERE `day` = DATE_FORMAT(NOW(), '%w')
但是我该如何处理过去的午夜问题呢?
它尝试了这样的事情,但不太对
劲 SELECT open_time, close_time, day, (CASE WHEN ((open_time <= close_time
AND open_time <= CURTIME() AND close_time >= CURTIME()) OR (open_time >= close_time
AND (CURTIME() <= close_time OR CURTIME() >= open_time))) THEN 'open' ELSE 'closed'
END) AS status FROM openhours WHERE bar_id = 2 and CASE WHEN (day = WEEKDAY(NOW())
AND (CURTIME() < open_time)) THEN CASE WHEN (day = (WEEKDAY(NOW()) - 1)
AND (CURTIME() < close_time)) THEN day = (WEEKDAY(NOW()) - 1)
ELSE day = WEEKDAY(NOW()) END ELSE day = WEEKDAY(NOW()) END
提前感谢!
您可以使用 UNIX_TIMESTAMP() 来处理此类问题:
SELECT `open_time`, `closed_time`, IF(UNIX_TIMESTAMP(CURTIME()) BETWEEN UNIX_TIMESTAMP(`open_time`) AND UNIX_TIMESTAMP(`closed_time`),'open','closed') AS `status` FROM `openhours` WHERE `day` = DATE_FORMAT(NOW(), '%w')
我希望我明白你的意思:)
你想要这样的东西
case closed < open
not between open and closed
else
between open and closed
当任一返回 true 时,柱线打开
一种选择是存储一周开始后的秒数,这样您根本不需要担心午夜。
将这些添加到您的表中:
open_time_i int(11)
closed_time_i int(11)
并使您的数据如下所示:
id: 1
bar_id: 1
open_time: 20:00:00
open_time_i: 72000
closed_time: 05:00:00
closed_time_i: 104400
day: 0
id: 2
bar_id: 1
open_time: 20:00:00
open_time_i: 158400
closed_time: 04:00:00
closed_time_i: 187200
day: 1
然后,您的查找可能如下所示:
$seconds = time() - strtotime('this week last sunday', date("Y-m-d", time()))
SELECT `open_time`, `closed_time`, IF(
$seconds BETWEEN `open_time_i` AND `closed_time_i`
OR
(("closed_time_i" >= 604800 AND $seconds BETWEEN 0 AND ("closed_time_i" - 604800)))
,'open','closed') AS `status` FROM `openhours`
第二个中间语句检查柱线是否在一周变化之后打开。