MySQL - Find the largest time difference between consecutive datetimes

Question

I have a table that contains a list of datetimes.

I'd like to find the longest amount of time between consecutive datetimes ie., find the largest distance between any two datetime entries that fall next to each other on a timeline. Think about it like a "longest streak" - the longest time from one reset to the next one chronologically.

For exmaple:

mysql> select * from resets order by datetime asc;
+----+---------------------+-------------+---------------------+---------------------+
| id | datetime            | activity_id | created_at          | updated_at          |
+----+---------------------+-------------+---------------------+---------------------+
|  7 | 2014-12-30 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  3 | 2014-12-31 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  5 | 2015-01-01 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  4 | 2015-01-02 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  6 | 2015-01-03 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  1 | 2015-01-04 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  2 | 2015-01-05 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |

 etc...

From the sample dataset above, I want to know which of the following time differences is greater:

• between rows 7 and 3
• between rows 3 and 5
• between rows 5 and 4
• etc...

(Obviously, they are all exactly 24 hours apart. I'm looking for a general solution.)

This is easily done with a normal programming language by iterating over the ordered array, storing the differences between consecutive values, and selecting the greatest one.

Is there a neat way to do this using only SQL?

Update:

The query that worked for me was

SELECT MAX(DATEDIFF(r.next_datetime, r.datetime))
    FROM (

    # finds each datetime and the next consecutive one in the table
    SELECT r1.datetime as datetime, (

        # finds the next consecutive datetime
        SELECT datetime
        FROM resets r2
        WHERE r2.datetime > r1.datetime
        ORDER BY datetime ASC
        LIMIT 1
    ) as next_datetime

    FROM resets as r1
    ORDER BY r1.datetime

) as r;

The innermost query SELECT datetime FROM resets r2... is responsible for finding the next datetime in the list that's greater than the current one. Notice that this query is ordered and has a limit of 1. This was the toughest part for me.

The rest is pretty straightforward. For each row in the table, we select the datetime value and the next consecutive datetime in the table. The outermost query finds the greatest difference between any of the datetime pairs we just created - the "winning streak".

I chose the answer that @OllieJones gave because it was the neatest and best explained, even if I prefer the "pure SQL" solution.

Answer 1

This query will compute time differences between successive rows and display the largest one -- the length of the winning streak. If you need the whole row, you'll need Gordon's query.

SELECT MAX(diff)
  FROM (
          SELECT TIMEDIFF(datetime,@prev) AS diff,
                 (@prev:=datetime) AS datetime  
            FROM resets, 
                 (SELECT @prev:=(SELECT MIN(datetime) FROM resets)) AS init
           ORDER BY datetime
       ) AS diffs

How does this work?

First of all, it's a cross-join between a one-row query and your table. The one-row query is this:

       (SELECT @prev:=(SELECT MIN(datetime) FROM resets)) 

It sets the user-defined value @prev to the lowest/earliest datetime in the table. This is a MySQL trick to initialize a user-defined variable at the beginning of the query.

Then, the SELECT clause has two columns in it:

          SELECT TIMEDIFF(datetime,@prev) AS diff,
                 (@prev:=datetime) AS datetime  

The first one takes the time difference between the present row's datetime and the value of @prev. The second one updates the value of @prev to the present row's datetime.

So the inner query spits out a list of the timestamps and the difference to the preceding timestamp in ORDER BY datetime.

The outer query SELECT MAX(diff) grabs the largest value of diff -- the longest winning streak -- from the inner query.

Let's be clear: This is MySQL-specific monkey business. Pure SQL is supposed to be declarative, not procedural. But this trick with the user-defined @prev variable lets us mix declarative and procedural code in a useful way, even if it's somewhat obscure.

Answer 2

You can calculate the next datetime using a correlated subquery and then find the biggest by sorting:

select r.*
from (select r.*,
             (select datetime
              from resets r2
              where r2.datetime > r.datetime
              order by datetime
              limit 1
             ) as next_datetime
      from resets r
     ) r
order by timestampdiff(second, datetime, next_datetime) desc
limit 1;