By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table `machine_shifts` (
`date` date ,
`shift_start_time` time ,
`shift_end_time` time ,
`shift` varchar (60),
`updated_on` timestamp
);
insert into `machine_shifts` (`date`, `shift_start_time`, `shift_end_time`, `shift`, `updated_on`) values('2010-01-01','06:00:00','14:30:00','A','2020-01-29 15:37:26'),
('2010-01-01','14:30:00','22:30:00','B','2020-01-29 15:37:26'),
('2010-01-01','22:30:00','06:00:00','C','2020-01-29 15:37:26'),
('2010-01-02','06:00:00','14:30:00','A','2020-01-29 15:37:26'),
('2010-01-02','14:30:00','22:30:00','B','2020-01-29 15:37:26'),
('2010-01-02','22:30:00','06:00:00','C','2020-01-29 15:37:26'),
('2010-01-03','06:00:00','14:30:00','A','2020-01-29 15:37:26'),
('2010-01-03','14:30:00','22:30:00','B','2020-01-29 15:37:26'),
('2010-01-03','22:30:00','06:00:00','C','2020-01-29 15:37:26'),
('2010-01-04','06:00:00','14:30:00','A','2020-01-29 15:37:26'),
('2010-01-04','14:30:00','22:30:00','B','2020-01-29 15:37:26'),
('2010-01-04','22:30:00','06:00:00','C','2020-01-29 15:37:27');
SELECT * FROM machine_shifts;
| date | shift_start_time | shift_end_time | shift | updated_on |
|---|---|---|---|---|
| 2010-01-01 | 06:00:00 | 14:30:00 | A | 2020-01-29 15:37:26 |
| 2010-01-01 | 14:30:00 | 22:30:00 | B | 2020-01-29 15:37:26 |
| 2010-01-01 | 22:30:00 | 06:00:00 | C | 2020-01-29 15:37:26 |
| 2010-01-02 | 06:00:00 | 14:30:00 | A | 2020-01-29 15:37:26 |
| 2010-01-02 | 14:30:00 | 22:30:00 | B | 2020-01-29 15:37:26 |
| 2010-01-02 | 22:30:00 | 06:00:00 | C | 2020-01-29 15:37:26 |
| 2010-01-03 | 06:00:00 | 14:30:00 | A | 2020-01-29 15:37:26 |
| 2010-01-03 | 14:30:00 | 22:30:00 | B | 2020-01-29 15:37:26 |
| 2010-01-03 | 22:30:00 | 06:00:00 | C | 2020-01-29 15:37:26 |
| 2010-01-04 | 06:00:00 | 14:30:00 | A | 2020-01-29 15:37:26 |
| 2010-01-04 | 14:30:00 | 22:30:00 | B | 2020-01-29 15:37:26 |
| 2010-01-04 | 22:30:00 | 06:00:00 | C | 2020-01-29 15:37:27 |
SET @start:='2010-01-02 00:00:00';
SET @end:='2010-01-03 10:00:00';
SELECT * FROM machine_shifts
WHERE CONCAT(`date`, ' ', shift_start_time) <= @end
AND
CONCAT(`date`, ' ', shift_end_time) + INTERVAL (shift = 'C') DAY >= @start
ORDER BY `date`, shift_start_time ASC;
| date | shift_start_time | shift_end_time | shift | updated_on |
|---|---|---|---|---|
| 2010-01-01 | 22:30:00 | 06:00:00 | C | 2020-01-29 15:37:26 |
| 2010-01-02 | 06:00:00 | 14:30:00 | A | 2020-01-29 15:37:26 |
| 2010-01-02 | 14:30:00 | 22:30:00 | B | 2020-01-29 15:37:26 |
| 2010-01-02 | 22:30:00 | 06:00:00 | C | 2020-01-29 15:37:26 |
| 2010-01-03 | 06:00:00 | 14:30:00 | A | 2020-01-29 15:37:26 |
SET @start:='2010-01-01 07:01:00';
SET @end:='2010-01-01 14:00:00';
SELECT * FROM machine_shifts
WHERE CONCAT(`date`, ' ', shift_start_time) <= @end
AND
CONCAT(`date`, ' ', shift_end_time) + INTERVAL (shift = 'C') DAY >= @start
ORDER BY `date`, shift_start_time ASC;
| date | shift_start_time | shift_end_time | shift | updated_on |
|---|---|---|---|---|
| 2010-01-01 | 06:00:00 | 14:30:00 | A | 2020-01-29 15:37:26 |