Calculate the time difference between of two rows

Question

I have a table with column StartDate, I want to calculate the time difference between two consecutive record.

Thanks.

---

@ Mark Byers and @ Yahia, I have request table as requestId, startdate

requestId    startdate                1            2011-10-16 13:15:56 2            2011-10-16 13:15:59 3            2011-10-16 13:15:59 4            2011-10-16 13:16:02 5            2011-10-16 13:18:07 

and i want to know what is the time difference between requestid 1 & 2, 2 & 3, 3 & 4 and so on. i know i will need self join on table, but i am not getting correct on clause.

Answer 1

The accepted answer is correct but gives the difference of numbers. As an example if I have the following 2 timestamps:

2014-06-09 09:48:15 2014-06-09 09:50:11 

The difference is given as 196. This is simply 5011 - 4815. In order to get the time difference, you may modify the script as follows:

SELECT A.requestid, A.starttime, TIMESTAMPDIFF(MINUTE,A.starttime,B.starttime) AS timedifference  FROM MyTable A INNER JOIN MyTable B ON B.requestid = (A.requestid + 1)  ORDER BY A.requestid ASC 

Answer 2

To achieve what you are asking try the following (UPDATE after edit from OP):

SELECT A.requestid, A.starttime, (B.starttime - A.starttime) AS timedifference FROM MyTable A INNER JOIN MyTable B ON B.requestid = (A.requestid + 1) ORDER BY A.requestid ASC 

IF requestid is not consecutive then you can use

SELECT A.requestid, A.starttime, (B.starttime - A.starttime) AS timedifference FROM MyTable A CROSS JOIN MyTable B WHERE B.requestid IN (SELECT MIN (C.requestid) FROM MyTable C WHERE C.requestid > A.requestid) ORDER BY A.requestid ASC