By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.27 |
create table table1 (date date, start time, end time) ;
insert into table1 values
('2021-12-04', '10:15:00', '13:05:00'),
('2021-12-04', '10:16:00', '13:05:00'),
('2021-12-04', '10:17:00', '13:05:00'),
('2021-12-04', '12:18:00', '15:38:00'),
('2021-12-04', '12:19:00', '15:38:00'),
('2021-12-04', '12:20:00', '15:38:00'),
('2021-12-04', '12:21:00', '15:38:00'),
('2021-12-04', '12:22:00', '15:38:00')
select * from table1
| date | start | end |
|---|---|---|
| 2021-12-04 | 10:15:00 | 13:05:00 |
| 2021-12-04 | 10:16:00 | 13:05:00 |
| 2021-12-04 | 10:17:00 | 13:05:00 |
| 2021-12-04 | 12:18:00 | 15:38:00 |
| 2021-12-04 | 12:19:00 | 15:38:00 |
| 2021-12-04 | 12:20:00 | 15:38:00 |
| 2021-12-04 | 12:21:00 | 15:38:00 |
| 2021-12-04 | 12:22:00 | 15:38:00 |
select substr(start, 1, 2) as hour
, count(start) as people
from table1
group by 1
order by 1
| hour | people |
|---|---|
| 10 | 3 |
| 12 | 5 |
select concat(substr(start, 1, 2), ':00-', substr(start, 1, 2), ':59') as hour
, count(start) as people
from table1
group by 1
order by 1
| hour | people |
|---|---|
| 10:00-10:59 | 3 |
| 12:00-12:59 | 5 |