Is it possible to compare timestamps between rows?

Question

I have a MySQL table reqs with (this is simplified from actual):

| Field                 | Type                  | Null | Key | Default | Extra |
+-----------------------+-----------------------+------+-----+---------+-------+
| time                  | datetime              | YES  |     | NULL    |       |
| user                  | varbinary(40)         | YES  |     | NULL    |       |
| foo                   | mediumint(8) unsigned | YES  |     | NULL    |       |
| bar                   | varbinary(20)         | YES  |     | NULL    |       |
| other fields          | ...

I want to find duplicate user-foo-bar combinations near one another in time — say, within 60 seconds of one another. So I want something like

select [whatever] from reqs inner join (
select user,foo,bar, count(*) as count from reqs
group by user,foo,bar having count>1
) as dups
where dups.user=reqs.user
and dups.foo = reqs.foo
and dups.bar=reqs.bar
and [something indicating the times are near each other];

— or at least that's the idea (in other words, I'm not sure whether the above is the way to structure the query, but the above is representative of the result I want from the query).

How would I do this?

Note that reqs is a huge table, so minimizing joins would be ideal.

Answer 1

The problem is, when you GROUP on user,foo,bar you are not able to get a list of each of those times to compare.

Something like this might work, but no idea on the performance on a large table. You might want to only do this on a subset (ie tuples just from the last 30 days)

SELECT A.*,B.time FROM reqs A
INNER JOIN (SELECT user,foo,bar,time FROM reqs) B 
 USING (user,foo,bar)
WHERE A.time!=B.time AND ABS(TIMESTAMPDIFF(SECOND, TIMESTAMP(A.time), TIMESTAMP(B.time))) < 60
GROUP BY A.user,A.foo,A.bar,A.time;