MySQL 8.0

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

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