Как выполнить запрос что бы отфильтровать по временным интевалам?

Question

[Сергей Ержович]

Есть данные
TEST:
Пн: 06:00-21:00 перерыв: 12:00-13:00, 17:00-18:00
QWERY INC.:
Пн: 03:00-17:00 перерыв: 08:00-08:15

Допустим сегодня понедельник 12:30 значит должно выдать только QWERTY INC,

Структура таблицы

CREATE TABLE IF NOT EXISTS `companies` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `companies` (`id`, `name`) VALUES
	(1, 'TEST'),
	(2, 'QWERTY INC.');
    
    


CREATE TABLE IF NOT EXISTS `schedule` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `day_of_week` tinyint(1) unsigned NOT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `schedule` (`id`, `company_id`, `day_of_week`, `start_time`, `end_time`) VALUES
	(1, 1, 1, '06:00:00', '21:00:00'),
	(2, 2, 1, '03:00:00', '17:00:00');
    
    
    
    
CREATE TABLE IF NOT EXISTS `breaks` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `day_of_week` tinyint(1) unsigned NOT NULL,
  `break_start` time DEFAULT NULL,
  `break_end` time DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `breaks` (`id`, `company_id`, `day_of_week`, `break_start`, `break_end`) VALUES
	(1, 1, 1, '12:00:00', '13:00:00'),
	(2, 1, 1, '17:00:00', '18:00:00'),
	(3, 2, 1, '08:00:00', '08:15:00');

Попытка сделать запрос

SET @this_time = '12:30:00', @this_day = 1; 
SELECT * FROM companies AS C

JOIN schedule AS S ON C.id = S.company_id AND S.day_of_week = @this_day
JOIN breaks AS B ON C.id = B.company_id AND B.day_of_week = @this_day 

WHERE @this_time BETWEEN S.start_time AND S.end_time 
AND @this_time NOT BETWEEN B.break_start AND B.break_end

онлайн черновик: https://www.db-fiddle.com/f/wJEg3J2wAGBkF6WcvpDJqV/0

PS делаю структуру БД с расписанием на неделю с учётом выходных и перерывов.

Думаю по такой схеме попробовать:

Answer 1

[Rsa97]

SELECT *
  FROM `companies`
  WHERE `id` IN (
    SELECT `company_id`
      FROM `schedule`
      WHERE `day_of_week` = 1 
        AND `start_time` <= '12:30'
        AND `end_time` > '12:30'
  )
    AND `id` NOT IN (
      SELECT `company_id`
        FROM `breaks`
        WHERE `day_of_week` = 1
          AND `break_start` <= '12:30'
          AND `break_end` > '12:30'
    )

Comments

[Сергей Ержович]

А через join так никак не получится? слышал что join быстрее работает) И как вообще такая схема для расписания? может есть ещё какие то варианты реализовать такое получше?

[Rsa97]

Сергей Ержович, Первую часть условия можно через JOIN. Для второй части понадобится LEFT JOIN и IS NULL, что может оказаться медленнее, чем NOT IN. Надо изучать EXPLAIN разных вариантов запроса.

Схема выглядит рабочей. Возможно стоит добавить ещё таблицы для исключений (например, праздничных дней), где вместо дней недели будут стоять даты. Но это уже зависит от задачи.

[Сергей Ержович]

Rsa97, нашёл в этой схеме изъян: если рабочий день или перерыв переваливает через сутки (например начало рабочего дня в 23:00 конец в 06:00)

Чуток переделал запрос, что бы такого косяка не было:

SQL QUERY

SET @this_time = '23:55:00', @this_day = 1; 

SELECT 
  * 
FROM 
  `companies` 
WHERE 
  `id` IN (
    SELECT 
      `company_id` 
    FROM 
      `schedule` 
    WHERE 
      `day_of_week` = @this_day 
      AND `start_time` > `end_time` 
      AND NOT `start_time` <= @this_time 
      AND `end_time` > @this_time 
      OR `start_time` > `end_time` 
      AND `start_time` <= @this_time 
      OR `start_time` <= @this_time 
      AND `end_time` > @this_time
  ) 
  AND `id` NOT IN (
    SELECT 
      `company_id` 
    FROM 
      `breaks` 
    WHERE 
      `day_of_week` = @this_day 
      AND `break_start` > `break_end` 
      AND `break_start` <= @this_time 
      AND `break_end` < @this_time 
      OR `break_start` > `break_end` 
      AND `break_end` > @this_time 
      OR `break_start` <= @this_time 
      AND `break_end` > @this_time
  )

Возможно надо отрефакторить и может что упустил? кстати насчёт отдельной таблицы с исключениями тоже подумал, хорошая идея