Pfthb

This is a view from my table where i only selected the DateTime and id.
What i am looking for i a query that only shows records which aren't within an ID range of 5 of each other. Or which are more than a second different from each other.

---------------------+--------+
| DateTime | id |
+---------------------+--------+
| 2018-06-02 16:10:13 | 61863 |
| 2018-06-03 14:04:13 | 63715 |
| 2018-06-03 17:34:28 | 64339 |
| 2018-06-04 14:20:55 | 67227 |
| 2018-06-04 14:20:56 | 67228 |
| 2018-06-04 15:39:47 | 67845 |
| 2018-06-04 17:07:12 | 68085 |
| 2018-06-04 17:07:13 | 68086 |
| 2018-06-04 17:51:11 | 68197 |
| 2018-06-04 17:51:12 | 68199 |
| 2018-06-05 05:22:59 | 68518 |
| 2018-06-05 05:23:00 | 68519 |
| 2018-06-07 10:28:28 | 74568 |
| 2018-06-07 16:18:36 | 76386 |

so my table would look like this:

---------------------+--------+
| DateTime | id |
+---------------------+--------+
| 2018-06-02 16:10:13 | 61863 |
| 2018-06-03 14:04:13 | 63715 |
| 2018-06-03 17:34:28 | 64339 |
| 2018-06-04 14:20:55 | 67227 |
| 2018-06-04 15:39:47 | 67845 |
| 2018-06-04 17:07:12 | 68085 |
| 2018-06-04 17:51:11 | 68197 |
| 2018-06-05 05:22:59 | 68518 |
| 2018-06-07 10:28:28 | 74568 |
| 2018-06-07 16:18:36 | 76386 |

It doesn't matter if 2018-06-04 14:20:55 or if 2018-06-04 14:20:56 gets shown in the query. Same goes for the rest, as long as it's one of them

I've tried this query but it only works if the ID's are contiginous

 SELECT t0.*, t1.id
 FROM table t0
 LEFT JOIN table t1
 ON t0.id + 1 = t1.id
 WHERE t1.id IS NULL;

You can use NOT EXISTS like so:

SELECT *
FROM yourdata t
WHERE NOT EXISTS (
 SELECT 1
 FROM yourdata x
 WHERE (x.DateTime > t.DateTime AND x.DateTime <= t.DateTime + INTERVAL 1 SECOND)
 OR (x.id > t.id AND x.id <= t.id + 4)
)

Rows with same datetime or id are NOT counted.

select t1.Datetime, t1.id
from mytable t1
join mytable t2
on t2.Datetime=(select Datetime from mytable t2 
where t2.Datetime > t1.Datetime 
and t2.id-t1.id > 5
limit 1)
where timestampdiff(second,t1.Datetime,t2.Datetime)>1;