By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (`time` TIMESTAMP);
INSERT INTO test VALUES
(FROM_UNIXTIME(1583060400)),
(FROM_UNIXTIME(1583060401)),
(FROM_UNIXTIME(1583060460)),
(FROM_UNIXTIME(1583074860));
SELECT * FROM test;
| time |
|---|
| 2020-03-01 11:00:00 |
| 2020-03-01 11:00:01 |
| 2020-03-01 11:01:00 |
| 2020-03-01 15:01:00 |
SELECT MAX(counter) groups_amount
FROM ( SELECT CASE WHEN TIMESTAMPDIFF(SECOND, @previous, `time`) > 1
THEN @counter := @counter + 1
END counter,
@previous := `time`
FROM test
CROSS JOIN ( SELECT @previous := '1970-01-01 00:00:01',
@counter := 0 ) init_vars
ORDER BY `time` ASC ) subquery;
| groups_amount |
|---|
| 3 |