mysql - MySQL查找打开时间和关闭时间之间的记录

Question

我们在一周中的特定日子有多家餐厅的营业和关闭时间。我们有通宵营业的餐厅。例如餐厅时间是(10:00 AM Monday - 05:00 AM Tuesday)

我正在尝试以下 SQL 查询。但记录返回计数为零。

数据库：

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

INSERT INTO `restaurants_hours` (`id`, `shop_id`, `day_of_week`, `open_time`, `close_time`)
VALUES
(1, 1, 'sunday', '10:00:00', '05:00:00'),
(2, 1, 'monday', '10:00:00', '05:00:00'),
(3, 1, 'tuesday', '10:00:00', '05:00:00'),
(4, 1, 'wednesday', '10:00:00', '05:00:00'),
(5, 1, 'thursday', '10:00:00', '05:00:00'),
(6, 1, 'friday', '10:00:00', '05:00:00'),
(7, 1, 'saturday', '10:00:00', '05:00:00');

询问

SELECT id, open_time, close_time, day_of_week FROM restaurants_hours WHERE
(((close_time > open_time) AND (FIND_IN_SET('thursday', day_of_week)) 
AND (open_time <= '12:40') AND (close_time >= '12:40') )
OR
((close_time <= open_time) AND (FIND_IN_SET('wednesday', day_of_week)) AND (close_time > '12:40')))

标签： mysql