MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.30


CREATE TABLE test (
  create_time datetime NOT NULL,
  id int unsigned NOT NULL,
  users smallint unsigned DEFAULT NULL,
    PRIMARY KEY (create_time, id)
);
insert into `test` (`create_time`, `id`, `users`) values
  ('2023-05-20 08:00:03','4','2567'),  
  ('2023-05-20 08:00:03','8','3674'),  
  ('2023-05-20 08:00:03','10','1169'),  
  ('2023-05-20 08:05:03','4','2564'),  
  ('2023-05-20 08:05:03','8','3671'),  
  ('2023-05-20 08:05:03','10','1169'),  
  ('2023-05-20 08:10:03','4','2560'),  
  ('2023-05-20 08:10:03','8','3667'),  
  ('2023-05-20 08:10:03','10','1163'),  
  ('2023-05-20 08:15:03','4','2555'),  
  ('2023-05-20 08:15:03','8','3665'),  
  ('2023-05-20 08:15:03','10','1157'),  
  ('2023-05-20 08:20:02','4','2550'),  
  ('2023-05-20 08:20:02','8','3659'),  
  ('2023-05-20 08:20:02','10','1148'),  
  ('2023-05-20 08:25:03','4','2549'),  
  ('2023-05-20 08:25:03','8','3647'),  
  ('2023-05-20 08:25:03','10','1140'),  
  ('2023-05-20 08:30:03','4','2539'),  
  ('2023-05-20 08:30:03','8','3641'),  
  ('2023-05-20 08:30:03','10','1137'),  
  ('2023-05-20 08:35:02','4','2537'),  
  ('2023-05-20 08:35:02','8','3634'),  
  ('2023-05-20 08:35:02','10','1133'),  
  ('2023-05-20 08:40:02','4','2534'),  
  ('2023-05-20 08:40:02','8','3631'),  
  ('2023-05-20 08:40:02','10','1133'),  
  ('2023-05-20 08:45:02','4','2528'),  
  ('2023-05-20 08:45:02','8','3625'),  
  ('2023-05-20 08:45:02','10','1136'),  
  ('2023-05-20 08:50:03','4','2526'),  
  ('2023-05-20 08:50:03','8','3621'),  
  ('2023-05-20 08:50:03','10','1131'),  
  ('2023-05-20 08:55:03','4','2522'),  
  ('2023-05-20 08:55:03','8','3616'),  
  ('2023-05-20 08:55:03','10','1127'),  
  ('2023-05-21 08:00:04','4','1706'),  
  ('2023-05-21 08:00:04','8','2438'),  
  ('2023-05-21 08:00:04','10','741'),  
  ('2023-05-21 08:05:03','4','1702'),  
  ('2023-05-21 08:05:03','8','2432'),  
  ('2023-05-21 08:05:03','10','736'),  
  ('2023-05-21 08:10:04','4','1699'),  
  ('2023-05-21 08:10:04','8','2425'),  
  ('2023-05-21 08:10:04','10','733'),  
  ('2023-05-21 08:15:03','4','1700'),  
  ('2023-05-21 08:15:03','8','2421'),  
  ('2023-05-21 08:15:03','10','732'),  
  ('2023-05-21 08:20:03','4','1692'),  
  ('2023-05-21 08:20:03','8','2421'),  
  ('2023-05-21 08:20:03','10','722'),  
  ('2023-05-21 08:25:03','4','1688'),  
  ('2023-05-21 08:25:03','8','2418'),  
  ('2023-05-21 08:25:03','10','723'),  
  ('2023-05-21 08:30:04','4','1688'),  
  ('2023-05-21 08:30:04','8','2420'),  
  ('2023-05-21 08:30:04','10','721'),  
  ('2023-05-21 08:35:02','4','1681'),  
  ('2023-05-21 08:35:02','8','2419'),  
  ('2023-05-21 08:35:02','10','720'),  
  ('2023-05-21 08:40:02','4','1674'),  
  ('2023-05-21 08:40:02','8','2414'),  
  ('2023-05-21 08:40:02','10','720'),  
  ('2023-05-21 08:45:03','4','1671'),  
  ('2023-05-21 08:45:03','8','2409'),  
  ('2023-05-21 08:45:03','10','722'),  
  ('2023-05-21 08:50:03','4','1666'),  
  ('2023-05-21 08:50:03','8','2408'),  
  ('2023-05-21 08:50:03','10','721'),  
  ('2023-05-21 08:55:03','4','1663'),  
  ('2023-05-21 08:55:03','8','2401'),  
  ('2023-05-21 08:55:03','10','718'),  
  ('2023-05-22 08:00:04','4','1752'),  
  ('2023-05-22 08:00:04','8','2528'),  
  ('2023-05-22 08:00:04','10','792'),  
  ('2023-05-22 08:05:03','4','1752'),  
  ('2023-05-22 08:05:03','8','2525'),  
  ('2023-05-22 08:05:03','10','790'),  
  ('2023-05-22 08:10:03','4','1744'),  
  ('2023-05-22 08:10:03','8','2523'),  
  ('2023-05-22 08:10:03','10','787'),  
  ('2023-05-22 08:15:03','4','1741'),  
  ('2023-05-22 08:15:03','8','2533'),  
  ('2023-05-22 08:15:03','10','783'),  
  ('2023-05-22 08:20:02','4','1730'),  
  ('2023-05-22 08:20:02','8','2540'),  
  ('2023-05-22 08:20:02','10','772'),  
  ('2023-05-22 08:25:03','4','1725'),  
  ('2023-05-22 08:25:03','8','2534'),  
  ('2023-05-22 08:25:03','10','770'),  
  ('2023-05-22 08:30:02','4','1720'),  
  ('2023-05-22 08:30:02','8','2529'),  
  ('2023-05-22 08:30:02','10','762'),  
  ('2023-05-22 08:35:02','4','1722'),  
  ('2023-05-22 08:35:02','8','2527'),  
  ('2023-05-22 08:35:02','10','763'),  
  ('2023-05-22 08:40:02','4','1723'),  
  ('2023-05-22 08:40:02','8','2529'),  
  ('2023-05-22 08:40:02','10','761'),  
  ('2023-05-22 08:45:02','4','1723'),  
  ('2023-05-22 08:45:02','8','2537'),  
  ('2023-05-22 08:45:02','10','763'),  
  ('2023-05-22 08:50:03','4','1721'),  
  ('2023-05-22 08:50:03','8','2527'),  
  ('2023-05-22 08:50:03','10','761'),  
  ('2023-05-22 08:55:03','4','1718'),  
  ('2023-05-22 08:55:03','8','2528'),  
  ('2023-05-22 08:55:03','10','760'),  
  ('2023-05-23 08:00:03','4','2384'),  
  ('2023-05-23 08:00:03','8','3665'),  
  ('2023-05-23 08:00:03','10','1273'),  
  ('2023-05-23 08:05:02','4','2376'),  
  ('2023-05-23 08:05:02','8','3657'),  
  ('2023-05-23 08:05:02','10','1260'),  
  ('2023-05-23 08:10:03','4','2373'),  
  ('2023-05-23 08:10:03','8','3650'),  
  ('2023-05-23 08:10:03','10','1250'),  
  ('2023-05-23 08:15:03','4','2367'),  
  ('2023-05-23 08:15:03','8','3647'),  
  ('2023-05-23 08:15:03','10','1236'),  
  ('2023-05-23 08:20:03','4','2368'),  
  ('2023-05-23 08:20:03','8','3637'),  
  ('2023-05-23 08:20:03','10','1216'),  
  ('2023-05-23 08:25:03','4','2353'),  
  ('2023-05-23 08:25:03','8','3632'),  
  ('2023-05-23 08:25:03','10','1206'),  
  ('2023-05-23 08:30:04','4','2346'),  
  ('2023-05-23 08:30:04','8','3628'),  
  ('2023-05-23 08:30:04','10','1195'),  
  ('2023-05-23 08:35:03','4','2341'),  
  ('2023-05-23 08:35:03','8','3616'),  
  ('2023-05-23 08:35:03','10','1184'),  
  ('2023-05-23 08:40:03','4','2339'),  
  ('2023-05-23 08:40:03','8','3613'),  
  ('2023-05-23 08:40:03','10','1179'),  
  ('2023-05-23 08:45:04','4','2337'),  
  ('2023-05-23 08:45:04','8','3608'),  
  ('2023-05-23 08:45:04','10','1168'),  
  ('2023-05-23 08:50:02','4','2329'),  
  ('2023-05-23 08:50:02','8','3604'),  
  ('2023-05-23 08:50:02','10','1162'),  
  ('2023-05-23 08:55:03','4','2322'),  
  ('2023-05-23 08:55:03','8','3602'),  
  ('2023-05-23 08:55:03','10','1156'),  
  ('2023-05-24 08:00:03','4','2646'),  
  ('2023-05-24 08:00:03','8','4169'),  
  ('2023-05-24 08:00:03','10','1402'),  
  ('2023-05-24 08:05:02','4','2634'),  
  ('2023-05-24 08:05:02','8','4158'),  
  ('2023-05-24 08:05:02','10','1400'),  
  ('2023-05-24 08:10:02','4','2615'),  
  ('2023-05-24 08:10:02','8','4147'),  
  ('2023-05-24 08:10:02','10','1390'),  
  ('2023-05-24 08:15:03','4','2607'),  
  ('2023-05-24 08:15:03','8','4147'),  
  ('2023-05-24 08:15:03','10','1376'),  
  ('2023-05-24 08:20:02','4','2608'),  
  ('2023-05-24 08:20:02','8','4147'),  
  ('2023-05-24 08:20:02','10','1386'),  
  ('2023-05-24 08:25:03','4','2605'),  
  ('2023-05-24 08:25:03','8','4138'),  
  ('2023-05-24 08:25:03','10','1379'),  
  ('2023-05-24 08:30:04','4','2598'),  
  ('2023-05-24 08:30:04','8','4126'),  
  ('2023-05-24 08:30:04','10','1372'),  
  ('2023-05-24 08:35:02','4','2597'),  
  ('2023-05-24 08:35:02','8','4116'),  
  ('2023-05-24 08:35:02','10','1365'),  
  ('2023-05-24 08:40:02','4','2593'),  
  ('2023-05-24 08:40:02','8','4110'),  
  ('2023-05-24 08:40:02','10','1356'),  
  ('2023-05-24 08:45:03','4','2586'),  
  ('2023-05-24 08:45:03','8','4099'),  
  ('2023-05-24 08:45:03','10','1351'),  
  ('2023-05-24 08:50:02','4','2586'),  
  ('2023-05-24 08:50:02','8','4088'),  
  ('2023-05-24 08:50:02','10','1341'),  
  ('2023-05-24 08:55:03','4','2584'),  
  ('2023-05-24 08:55:03','8','4085'),  
  ('2023-05-24 08:55:03','10','1333');

Records: 180  Duplicates: 0  Warnings: 0


select create_time, sum(users) as ut from test where date(create_time) = '2023-05-24' group by create_time;


create_time
ut


2023-05-24 08:00:03
8217

2023-05-24 08:05:02
8192

2023-05-24 08:10:02
8152

2023-05-24 08:15:03
8130

2023-05-24 08:20:02
8141

2023-05-24 08:25:03
8122

2023-05-24 08:30:04
8096

2023-05-24 08:35:02
8078

2023-05-24 08:40:02
8059

2023-05-24 08:45:03
8036

2023-05-24 08:50:02
8015

2023-05-24 08:55:03
8002


select max(t.ut) from ( SELECT create_time, SUM(users) AS ut FROM test WHERE DATE(create_time) = '2023-05-24' GROUP BY create_time) t;


max(t.ut)


8217


select create_time from 
( select create_time, sum(users) as ut from test where date(create_time) = '2023-05-24' group by create_time) t2
 where t2.ut = ( select max(t.ut) as ut from ( SELECT create_time, SUM(users) AS ut FROM test WHERE DATE(create_time) = '2023-05-24' GROUP BY create_time) t );


create_time


2023-05-24 08:00:03


SELECT DISTINCT
    id,
    create_time AS stat_time,
    users
FROM test
WHERE create_time = (
SELECT create_time FROM 
( SELECT create_time, SUM(users) AS ut FROM test WHERE DATE(create_time) = '2023-05-24' GROUP BY create_time) t2
 WHERE t2.ut = ( SELECT MAX(t.ut) AS ut FROM ( SELECT create_time, SUM(users) AS ut FROM test WHERE DATE(create_time) = '2023-05-24' GROUP BY create_time) t ));


id
stat_time
users


4
2023-05-24 08:00:03
2646

8
2023-05-24 08:00:03
4169

10
2023-05-24 08:00:03
1402
create_time	ut
2023-05-24 08:00:03	8217
2023-05-24 08:05:02	8192
2023-05-24 08:10:02	8152
2023-05-24 08:15:03	8130
2023-05-24 08:20:02	8141
2023-05-24 08:25:03	8122
2023-05-24 08:30:04	8096
2023-05-24 08:35:02	8078
2023-05-24 08:40:02	8059
2023-05-24 08:45:03	8036
2023-05-24 08:50:02	8015
2023-05-24 08:55:03	8002
id	stat_time	users
4	2023-05-24 08:00:03	2646
8	2023-05-24 08:00:03	4169
10	2023-05-24 08:00:03	1402