MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.25


CREATE TABLE `timeslot` (
  `Timeslot_ID` int(100) NOT NULL,
  `ExamEvent_ID` int(100) NOT NULL,
  `Date` varchar(100) NOT NULL,
  `Start_Time` varchar(100) NOT NULL,
  `End_Time` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `timeslot` (`Timeslot_ID`, `ExamEvent_ID`, `Date`, `Start_Time`, `End_Time`) VALUES
(1, 1, 'September 24, 2020', '9:00 AM', '10:00 AM'),
(2, 1, 'September 23, 2020', '9:00 AM', '10:00 AM'),
(3, 1, 'September 23, 2020', '8:00 AM', '9:00 AM'),
(4, 1, 'September 25, 2020', '8:00 AM', '9:00 AM'),
(5, 1, 'September 26, 2020', '9:00 AM', '10:30 AM');
ALTER TABLE `timeslot`
  ADD PRIMARY KEY (`Timeslot_ID`);
COMMIT;
SELECT Timeslot_ID, ExamEvent_ID, Date, Start_Time, End_Time
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Date ORDER BY STR_TO_DATE (Start_Time, '%l:%i %p')) rn
  FROM timeslot
) t  
ORDER BY rn, STR_TO_DATE(Date, '%M %e, %Y')


Timeslot_ID
ExamEvent_ID
Date
Start_Time
End_Time


3
1
September 23, 2020
8:00 AM
9:00 AM

1
1
September 24, 2020
9:00 AM
10:00 AM

4
1
September 25, 2020
8:00 AM
9:00 AM

5
1
September 26, 2020
9:00 AM
10:30 AM

2
1
September 23, 2020
9:00 AM
10:00 AM

Timeslot_ID	ExamEvent_ID	Date	Start_Time	End_Time
3	1	September 23, 2020	8:00 AM	9:00 AM
1	1	September 24, 2020	9:00 AM	10:00 AM
4	1	September 25, 2020	8:00 AM	9:00 AM
5	1	September 26, 2020	9:00 AM	10:30 AM
2	1	September 23, 2020	9:00 AM	10:00 AM