CODEWITHSUNDEEP
mysqlsqltimestamp
scabbage
scabbage

Reputation: 1502

Find the min time interval between rows for a MySQL dataset

I have a MySQL table that records the events received for a user and the Date a event is received.

Date                    User

2012-01-21 18:30:02     AAA
2012-01-21 18:30:05     AAA
2012-01-21 18:30:08     AAA
2012-01-21 18:30:11     AAA
2012-01-21 18:30:15     AAA
2012-01-21 18:30:18     AAA
2012-01-21 18:30:21     AAA
2012-01-21 18:30:23     AAA
2012-01-21 18:30:26     AAA
2012-01-21 18:30:29     BBB
2012-01-21 18:30:32     BBB
2012-01-21 18:30:33     BBB
2012-01-21 18:30:37     BBB
2012-01-21 18:30:40     BBB
2012-01-21 18:30:42     BBB
2012-01-21 18:30:44     BBB
2012-01-21 18:31:01     BBB
2012-01-21 18:31:04     BBB
2012-01-21 18:31:07     BBB
2012-01-21 18:31:10     BBB

The events are not sorted by Date or User.

I would like to find out the min of the time interval (in seconds) between two successive events for a single user. So the result set would look like this:

MIN_INTERVAL        USER

3                   AAA
5                   BBB

Can anyone help me come up a SQL query that generate this? I don't think a GROUP BY will help.

Upvotes: 0

Views: 1579

Answers (3)

Andrew
Andrew

Reputation: 4624

Simple version might not be the fastest?

SELECT t1.user, MIN(TIMESTAMPDIFF(SECOND, t1.date, t2.date))
FROM tbl AS t1
JOIN tbl AS t2 ON t1.user = t2.user
WHERE t1.date < t2.date
GROUP BY t1.user

Upvotes: 1

Zane Bien
Zane Bien

Reputation: 23125

This can be accomplished by shifting a self-join by 1 record (in order of date) which will get times and their immediate previous times onto the same row so we can do a seconds difference between the two:

SELECT 
    a.user,
    MIN(TIMESTAMPDIFF(SECOND, b.date, a.date)) AS mindiff
FROM
(
    SELECT user, date, @val1:=@val1+1 AS rn
    FROM tbl
    CROSS JOIN (SELECT @val1:=0) AS val1_init
    ORDER BY date
) a
INNER JOIN
(
    SELECT user, date, @val2:=@val2+1 AS rn
    FROM tbl
    CROSS JOIN (SELECT @val2:=1) AS val2_init
    ORDER BY date
) b ON a.rn = b.rn
GROUP BY a.user

SQLFiddle Demo

Intermediate Results (Before GROUP BY)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270513

In mysql, you need to do this using a self join and two group bys:

select t.user, min(diff)
from (select t.user, unix_timestamp(tnext.date) - unix_timestamp(t.date) as diff
      from t join
           tnext
           on t.user = tnext.user and
              t.date < tnext.date
      group by t.user, t.date
     ) t
group by user

The inner subquery finds the next time and subtracts the values to get the diff. The outer returns the smallest diff.

Upvotes: 0

Related Questions