How to execute a query to filter by time intervals?

Question

MySQL

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,

Table structure

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

Attempt to make a request

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

online draft: https://www.db-fiddle.com/f/wJEg3J2wAGBkF6WcvpDJqV/0

PS I am making a database structure with a weekly schedule, taking into account weekends and breaks.

I'm thinking of trying something like this:

Answer 1

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