An interesting SQL solution.

Earlier today, someone in the #uphpu channel posed an interesting question about how to do something in SQL (in particular MySQL). The question was "given a table with an auto-incrementing id and a timestamp field, is there a way to return the time difference between one row and the next row?". I thought about that question and came up with this solution:

SELECT TIMESTAMPDIFF(SECOND,t1.time,t2.time)
AS difference
FROM example
AS t1, example AS t2
WHERE t1.id+1 = t2.id;

This query will return the difference between two rows by basically comparing the table against itself. The last part of the query (in the WHERE clause) tells mysql to compare a row with an id of i with the row of id i+1. It will return something like this:

mysql> SELECT TIMESTAMPDIFF(SECOND,t1.time,t2.time) 
AS difference 
FROM example 
AS t1, example 
AS t2 WHERE t1.id+1  = t2.id LIMIT 5;                                                
+------------+
| difference |
+------------+
| 2          |
| 13         |
| 11         |
| 1161       |
| 1          |
+------------+
5 rows in set (0.00 sec)

Note that this was done on a table with 100,000 rows and it literally took no time at all. The problem with this approach though, is that if the row id of i+1 doesn't exist then it isn't compared. Consider the following:

mysql>  SELECT
CONCAT("Calculated difference between id
",t1.id," and id ",t2.id," is ",
TIMESTAMPDIFF(SECOND,t1.time,t2.time)," seconds")
AS difference
FROM example AS t1,
example AS t2
WHERE t1.id+1=t2.id ORDER BY t1.id LIMIT 5;
+-------------------------------------------------------------+
| difference                                                  |
+-------------------------------------------------------------+
| Calculated difference between id 3 and id 4 is 13 seconds   |
| Calculated difference between id 4 and id 5 is 11 seconds   |
| Calculated difference between id 5 and id 6 is 1161 seconds |
| Calculated difference between id 6 and id 7 is 2 seconds    |
| Calculated difference between id 7 and id 8 is 1 seconds    |
+-------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> SELECT id,time FROM example ORDER BY id LIMIT 5;
+----+---------------------+
| id | time                |
+----+---------------------+
| 1  | 2006-11-14 15:31:47 |
| 3  | 2006-11-14 15:51:08 |
| 4  | 2006-11-14 15:51:21 |
| 5  | 2006-11-14 15:51:32 |
| 6  | 2006-11-14 16:10:53 |
+----+---------------------+
5 rows in set (0.10 sec)

As you can see, since there is no row with an id of 2, no difference is calculated and it starts at 3. Also, don't be scared by the CONCAT in that query, it's just a helpful little thing to show which rows it's comparing. It is functionally still the same query.


The problem is that the query does not compare the 'next' time value in the table if the x+1 row doesn't exist. It just skips it. The solution is to use a subselect like this:

mysql> SELECT CONCAT("Calculated difference between id ",t1.id,"
and id ",t2.id," is ",
TIMESTAMPDIFF(SECOND,t1.time,t2.time)," seconds")
AS difference
FROM example AS t1,
example AS t2
WHERE t2.id=(SELECT MIN(id) FROM example WHERE id>t1.id) LIMIT 5;
+-------------------------------------------------------------------+
| difference                                                        |
+-------------------------------------------------------------------+
| Calculated difference between id 1 and id 2 is -671231323 seconds |
| Calculated difference between id 2 and id 3 is 269130267 seconds  |
| Calculated difference between id 3 and id 4 is 359323703 seconds  |
| Calculated difference between id 4 and id 5 is -10739888 seconds  |
| Calculated difference between id 5 and id 6 is -131699346 seconds |
+-------------------------------------------------------------------+
5 rows in set (0.00 sec)

The query now selects the 'next' row in the table by selecting the the minimum (id) number that is greater than the row id you're comparing it to.

In plain english, it would look like this:

Current t1.id is 5, so find the lowest number row id that is greater than 5 to compare it to.