CODEWITHSUNDEEP
mysqlsqldatabase
Adam Meyer
Adam Meyer

Reputation: 1525

MySQL - Select rows whose timestamp is within a set time of another row

I have a table of events with timestamps, and types (1 or 0). Im looking to select all the type 0 rows where a type 1 row has a timestamp within 10 (or whatever) seconds. 

event_id    |   type    |   timestamp
1           |   0       |   2012-1-1 00:00:00
2           |   0       |   2012-1-1 00:00:01
3           |   1       |   2012-1-1 00:00:09
4           |   1       |   2012-1-1 00:00:10
5           |   0       |   2012-1-1 00:00:14
6           |   0       |   2012-1-1 00:00:20
7           |   1       |   2012-1-1 00:00:25
8           |   0       |   2012-1-1 00:00:40
9           |   0       |   2012-1-1 00:00:50
10          |   1       |   2012-1-1 00:01:00

So in this example it would grab rows 1,2, and 6

I know how to do it if I run a new query for each type 0 event, but obviously that can be incredibly slow once the table becomes thousands of rows.

Upvotes: 0

Views: 205

Answers (2)

valex
valex

Reputation: 24144

SQLFiddle demo

select distinct t1.* from t as t1
JOIN t as t2 on (t2.type=1) and 
                (t2.timestamp between t1.timestamp
                                  AND t1.timestamp + INTERVAL 10 SECOND
                 ) 
where t1.type=0

Upvotes: 1

Mureinik
Mureinik

Reputation: 311326

As you suggested, doing this query for each row would be inefficient. However, a JOIN seems to fit your need nicely:

SELECT ones.*
FROM   my_table ones
JOIN   my_table zeroes 
ON     zeroes.type = 0 AND 
       TIME_TO_SEC(TIMEDIFF(ones.timestamp, zeroes.timestamp)) <= 10
WHERE  ones.type = 1

Upvotes: 1

Related Questions