CODEWITHSUNDEEP
mysqldatetimeselectdatediff
zombocom
zombocom

Reputation: 29

MySQL: time between rows

I'm new to MySQL and I'm trying to figure out how to calculate the time passed between the rows in a log table.

The table is a basic table with ID , Hostname , Info , Timestamp, with data like:

+---+----------+-------------------+---------------------+
|ID | Hostname | Info              | Timestamp           |
+---+----------+-------------------+---------------------+
|445| switch1  | "port 1 inserted" | 2013-01-19 19:51:40 |
|446| switch1  | "port 2 inserted" | 2013-01-19 19:59:41 |
|447| Router2  | "alarm fan speed" | 2013-01-19 20:00:40 |
|448| switch1  | "alarm fan speed" | 2013-01-19 20:12:20 |
|449| Router2  | "alarm fan speed" | 2013-01-19 21:42:41 |
+---+----------+-------------------+---------------------+

So basically I want to get the time difference between the rows with the same HOSTNAME, in this case between row 445 and 446 it would result in 8 minutes 1 second. And between 446 and 448 it would result in 12 minutes and 39 seconds . And so on...

Any tips on this would be greatly appreciated.

Upvotes: 1

Views: 464

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29071

Try this: 

SELECT id, IF(@lastdate = '', NULL, TIMESTAMPDIFF(SECOND, TIMESTAMP, @lastdate)), 
       @lastdate:=TIMESTAMP 
FROM tablename, (SELECT @lastdate:='') a;

Upvotes: 0

Kermit
Kermit

Reputation: 34062

This will give you the time difference in seconds between rows:

SELECT c.info, 
       CASE 
         WHEN f.`timestamp` IS NOT NULL THEN 
         Timestampdiff(second, f.`timestamp`, 
         c.`timestamp`) 
         ELSE NULL 
       end AS time_diff 
FROM   (SELECT @rowa := @rowa + 1 AS id, 
               a.hostname, 
               a.info, 
               a.`timestamp` 
        FROM   sparkles a 
               JOIN (SELECT @rowa := 0) b 
        WHERE  a.hostname = 'switch1') c 
       LEFT JOIN (SELECT @rowb := @rowb + 1 AS id, 
                         d.hostname, 
                         d.info, 
                         d.`timestamp` 
                  FROM   sparkles d 
                         JOIN (SELECT @rowb := 0) e 
                  WHERE  d.hostname = 'switch1') f 
              ON f.id = c.id - 1

Result (for switch1 as the hostname)

|            INFO | TIME_DIFF |
-------------------------------
| port 1 inserted |    (null) |
| port 2 inserted |       481 |
| alarm fan speed |       759 |

See the demo

Result (for Router2 as the hostname)

|            INFO | TIME_DIFF |
-------------------------------
| alarm fan speed |    (null) |
| alarm fan speed |      6121 |

See the demo

Upvotes: 1

Related Questions