mysql 营业时间,查找下次营业时间； mysql小时计算

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?

Thanks in advance

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');

Edit:

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.

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).

解决方案

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`

Obsolete:

To find tomorrow's available open_times:

SELECT *

FROM `shop_hours`

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

Edit 2:

To find next available open_times:

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`

Edit:

DATE_FORMAT(*DATE*, '%w') uses the format 0 = Sunday ... 6 = Saturday

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)