Answer the question
In order to leave comments, you need to log in
How to execute a query to filter by time intervals?
Available TEST
data :
Mon: 06:00-21:00 break: 12:00-13:00, 17:00-18:00
QWERY INC.:
Mon: 03:00-17:00 break: 08:00-08 :15
Let's say today is Monday 12:30, so it should only issue 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
Answer the question
In order to leave comments, you need to log in
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'
)
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question