MySQL - find the largest time difference between consecutive dates

Question

I have a table containing a list of datetimes.

I would like to find the longest time span between consecutive dates, i.e. Find the largest distance between any two date and time records that fall next to each other on the timeline. Think of it as the “longest strip” —the longest time from one reset to the next in chronological order.

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 lines 7 and 3
• between lines 3 and 5
• between lines 5 and 4
• etc...

(Obviously, they are all in exactly 24 hours. I’m looking for a common solution.)

This is easily done using a common programming language by iterating over an ordered array, preserving the differences between consecutive values, and choosing the largest one.

Is there an easy way to do this using only SQL?

Update:

The request 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 SELECT datetime FROM resets r2... query SELECT datetime FROM resets r2... is responsible for finding the next datetime in a list that is larger than the current one. Please note that this request is streamlined and has a restriction of 1. This was the hardest part for me.

The rest is pretty simple. For each row in the table, we select the datetime value and the next sequential datetime in the table. The outermost query finds the biggest difference between any of the datetime data pairs that we just created - a "winning stroke".

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

Answer 1

This query will calculate the time difference between consecutive lines and display the largest of them - the length of the winning band. If you need a whole line, you will need a Gordon 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 it works?

First of all, this is a cross-connection between a single-row query and your table. A single line query is as follows:

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

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

Then the SELECT clause has two columns in it:

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

The first takes the time difference between the current datetime string and the @prev value. The second updates the @prev value to the current datetime string.

Thus, the internal query pops up a list of timestamps and the difference with the previous timestamp in ORDER BY datetime .

The outer SELECT MAX(diff) query captures the largest diff value - the longest winning row - from the inner query.

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

Answer 2

You can calculate the following datetime using a correlated subquery, and then find the largest one by sort:

 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; 

Answer 3

Unfortunately, MySQL does not support lag to get the previous datetime string, but you can emulate it using variables.

 select max(timediff(datetime,prevDate)) from ( select *, @prevDate prevDate, @prevDate := datetime from resets order by datetime ) t1 

or if you want to select the whole row

 select * from ( select *, @prevDate prevDate, @prevDate := datetime from resets order by datetime ) t1 order by timediff(datetime,prevDate) desc limit 1