Calculate the number of unique strings with the date, with a possible error

In the select, I get rows from the table with time in the format TIMESTAMP. I want to count unique rows, BUT with a possible error of 1 second. In the example below, for example, 3 unique records (1 and 2 have an error of 1 second, and therefore counted as one).

I was thinking to make a function like ABS(time_1 – time_2) > 1 to search for unique values.

  1. Is it possible to implement this somehow on the SQL side, or would it be better to implement it on the server-side, which would be pulling this data?
  2. Is it possible to do it without functions?
  3. How much of a burden will this put on the database?

Any tips for solving the problem are welcome!

ps: I have an old version of SQL 5.7

Example output:

+------------+
|  time      |
+------------+
| 1583060400 |
+------------+
| 1583060401 |
+------------+
| 1583060460 |
+------------+
| 1583074860 |
+------------+

Answer

Assuming that “if a row TIMESTAMP differs from previous row TIMESTAMP by not more than 1 second then ignore this row presence” you may use

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;

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=2aba3b8f473e65f4f40e449c8d97a79d