查找下一次营业时间； mysql小时计算 [英] Find next time business is open; mysql hours calculation

问题描述

我正在尝试确定一家商店当前是否在营业时间内，如果没有，那么请选择下一次营业.

I’m trying to figure out if a shop is currently within its opening hours, if not then select the next time its open.

最后，我需要将开幕日指定为特定日期.

Finally I need to be able to put the opening day as a specific date.

有人可以给我提示如何构造此查询吗?

Can someone possible give me a tip how to construct this query?

预先感谢

CREATE TABLE `shop_hours` (
  `id` int(11) NOT NULL,
  `shop_id` int(11) unsigned NOT NULL,
  `day_of_week` int(11) unsigned NOT NULL,
  `open_time` time NOT NULL,
  `close_time` time NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `shop_hours` (`id`, `shop_id`, `day_of_week`, `open_time`, `close_time`)
VALUES
    (1, 1, 0, '08:00:00', '24:00:00'),
    (2, 1, 1, '08:00:00', '24:00:00'),
    (3, 1, 2, '08:00:00', '24:00:00'),
    (4, 1, 3, '08:00:00', '24:00:00'),
    (5, 1, 4, '08:00:00', '24:00:00'),
    (6, 1, 5, '08:00:00', '24:00:00'),
    (7, 1, 6, '08:00:00', '24:00:00');

为了澄清一点，我不是要找到开放式商店，而只是寻找一个特定商店的营业时间.根据开放/关闭时间以及现在的时间.我将生成一些可选的时间戳记，将其增加15分钟.

To clarify a little I'm not looking to find open shops, but only open hours for ONE specific shop. Based on the opening/closing hour, and what time it is now. I will generate some selectable timestamps incremented by 15 minutes.

例如如果一家商店刚刚关门(1PM)，我将需要使用下一个营业日的开/关时间. (商店不一定每天都营业，也可以在周日关闭).

E.g. if a shop has just closed (1PM), I will need to use the next open day's open/closing time instead. (the shop isn't necessarily open every day, could be closed Sundays).

推荐答案

要查找shop_id，该商店对NOW()

To find out shop_id's, that is open for NOW()

SELECT *
  FROM `shop_hours`
 WHERE `day_of_week` = DATE_FORMAT(NOW(), '%w')
   AND CURTIME() BETWEEN `open_time` AND `close_time`

已过时:

要查找明天的可用open_time:

SELECT *
  FROM `shop_hours`
 WHERE `day_of_week` = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 DAY), '%w')

修改 2:

要查找下一个可用的open_time:

  SELECT `shop_id`,
         MIN(CAST(CONCAT(DATE(DATE_ADD(NOW(), INTERVAL ((7 + DATE_FORMAT(NOW(), '%w') - `day_of_week`) % 7) DAY)), ' ', `open_time`) AS DATETIME)) AS `next_open_datetime`
    FROM `shop_hours`
GROUP BY `shop_id`

修改:

DATE_FORMAT(*DATE*, '%w')使用格式0 = Sunday ... 6 = Saturday

如果要在day_of_week字段中使用ISO格式1 = Monday ... 7 = Sunday，则应将php的date('N')绑定到查询中(或使用Mysql的if函数IF(DATE_FORMAT(NOW(), '%w') = 0, 7, DATE_FORMAT(NOW(), '%w'))，但这很丑陋)

If you want to use the ISO format 1 = Monday ... 7 = Sunday in your day_of_week field, you should bind php's date('N') to your query (or use Mysql's if function IF(DATE_FORMAT(NOW(), '%w') = 0, 7, DATE_FORMAT(NOW(), '%w')), but that's ugly)

这篇关于查找下一次营业时间； mysql小时计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！