By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with CTE as
(SELECT 'bob' as Name, STR_TO_DATE('202405051300','%Y%m%d%H%i%s') TS UNION ALL
SELECT 'Ted' as Name, STR_TO_DATE('202405051306','%Y%m%d%H%i%s') TS UNION ALL
SELECT 'Alice' as Name, STR_TO_DATE('202405051307','%Y%m%d%H%i%s') TS UNION ALL
SELECT 'John' as Name, STR_TO_DATE('202405051308','%Y%m%d%H%i%s') TS UNION ALL
SELECT 'Denver' as Name, STR_TO_DATE('202405051311','%Y%m%d%H%i%s') TS)
SELECT Distinct A.*, B.MTS, B.GRP
FROM CTE A
INNER JOIN (SELECT truncate(DATE_FORMAT(TS, '%i')/5,0) as GRP, max(TS) MTS
FROM CTE
GROUP BY GRP) B
On A.TS = B.MTS
| Name | TS | MTS | GRP |
|---|---|---|---|
| bob | 2024-05-05 13:00:00 | 2024-05-05 13:00:00 | 0 |
| John | 2024-05-05 13:08:00 | 2024-05-05 13:08:00 | 1 |
| Denver | 2024-05-05 13:11:00 | 2024-05-05 13:11:00 | 2 |