Как выполнить запрос что бы отфильтровать по временным интевалам?
Есть данные
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) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4; 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; 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; 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'); |
CREATE TABLE IF NOT EXISTS `companies` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4; 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; 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; 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 |
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 делаю структуру БД с расписанием на неделю с учётом выходных и перерывов.
Думаю по такой схеме попробовать:
Дополнительно:
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' ) |
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' )
- А через join так никак не получится? слышал что join быстрее работает) И как вообще такая схема для расписания? может есть ещё какие то варианты реализовать такое получше?
- Сергей Ержович, Первую часть условия можно через JOIN. Для второй части понадобится LEFT JOIN и IS NULL, что может оказаться медленнее, чем NOT IN. Надо изучать EXPLAIN разных вариантов запроса.
Схема выглядит рабочей. Возможно стоит добавить ещё таблицы для исключений (например, праздничных дней), где вместо дней недели будут стоять даты. Но это уже зависит от задачи.
- Rsa97, нашёл в этой схеме изъян: если рабочий день или перерыв переваливает через сутки (например начало рабочего дня в 23:00 конец в 06:00)
Чуток переделал запрос, что бы такого косяка не было:
SQL QUERYSET @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 )
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 )
Возможно надо отрефакторить и может что упустил? кстати насчёт отдельной таблицы с исключениями тоже подумал, хорошая идея
Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.
Пока нет других ответов. Будьте первым, кто поможет автору.
Ответить на вопрос
Для выполнения запроса с фильтрацией по временным интервалам вам необходимо использовать операторы сравнения в SQL. В зависимости от того, как у вас хранятся временные данные в базе данных, вы можете использовать различные функции и операторы для выполнения фильтрации.
Если у вас в базе данных есть поле типа DATETIME или TIMESTAMP, то запрос будет выглядеть примерно так:
SELECT * FROM your_table WHERE your_datetime_column >= '2022-01-01 00:00:00' AND your_datetime_column <= '2022-01-31 23:59:59';
В данном примере мы выбираем все записи из таблицы `your_table`, где значение поля `your_datetime_column` находится в интервале от 1 января 2022 года до 31 января 2022 года.
Если у вас хранятся данные в формате UNIX timestamp, то запрос будет выглядеть по-другому:
SELECT * FROM your_table WHERE your_unix_timestamp_column >= UNIX_TIMESTAMP('2022-01-01 00:00:00') AND your_unix_timestamp_column <= UNIX_TIMESTAMP('2022-01-31 23:59:59');
Здесь мы используем функцию `UNIX_TIMESTAMP()` для преобразования даты в UNIX timestamp и затем сравниваем значения.
Помните, что формат даты и времени может отличаться в разных СУБД, поэтому убедитесь, что вы используете правильный формат для вашей базы данных. Кроме того, обязательно проверьте данные на предмет корректности и защитите запрос от SQL-инъекций, если это необходимо.
Надеюсь, этот ответ поможет вам выполнить запрос с фильтрацией по временным интервалам в вашей базе данных. Если у вас возникнут дополнительные вопросы, не стесняйтесь задавать их!