[adsense id=”0514458240″ width=”468″ height=”60″]
This post shows a solution to get the time difference between successive records in a table. If we are basing out solution on an primary key identifier say id, we cannot make the assumption that the id-1 represents the previous identifier (there may be gaps in the primary key). So using the ORDER BY clause should determine the order in which successive records appear. We can never assume any kind of order being returned, hence the importance of having the ORDER BY clause.
For this solution we make use of the user system variables to keep track of the previous row’s time.
In this example we have a table with a simple id and starttime. The second starttime represents the endtime for the first row and so on.
mysql> select * from time_links order by starttime;
+----+-----------+
| id | starttime |
+----+-----------+
| 5 | 09:00:30 |
| 7 | 09:01:00 |
| 8 | 09:01:01 |
| 6 | 09:01:59 |
| 2 | 09:09:01 |
| 3 | 09:09:30 |
| 4 | 09:09:59 |
| 1 | 17:05:23 |
+----+-----------+
8 rows in set (0.00 sec)
mysql> SELECT id,
-> Sec_to_time(@diff) AS starttime,
-> starttime endtime,
-> IF(@diff = 0, 0, Time_to_sec(starttime) - @diff) AS diff,
-> @diff := Time_to_sec(starttime)
-> FROM time_links,
-> (SELECT @diff := 0) AS x
-> ORDER BY time_links.starttime;
+----+-----------+----------+-------+---------------------------------+
| id | starttime | endtime | diff | @diff := Time_to_sec(starttime) |
+----+-----------+----------+-------+---------------------------------+
| 5 | 00:00:00 | 09:00:30 | 0 | 32430 |
| 7 | 09:00:30 | 09:01:00 | 30 | 32460 |
| 8 | 09:01:00 | 09:01:01 | 1 | 32461 |
| 6 | 09:01:01 | 09:01:59 | 58 | 32519 |
| 2 | 09:01:59 | 09:09:01 | 422 | 32941 |
| 3 | 09:09:01 | 09:09:30 | 29 | 32970 |
| 4 | 09:09:30 | 09:09:59 | 29 | 32999 |
| 1 | 09:09:59 | 17:05:23 | 28524 | 61523 |
+----+-----------+----------+-------+---------------------------------+
8 rows in set (0.00 sec)