CODEWITHSUNDEEP
mysql
user1019444
user1019444

Reputation: 263

Calculate the time difference between of two rows

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.

Upvotes: 25

Views: 37425

Answers (4)

Nick
Nick

Reputation: 147266

In MySQL 8+, you can use the LEAD window function to get the value from the next row (where next is defined as the row having the next higher requestId):

SELECT *, 
    SEC_TO_TIME(TIMESTAMPDIFF(SECOND, startdate, LEAD(startdate) OVER (ORDER BY requestId))) AS diff
FROM mytable

Output for your sample data:

requestId   startdate               diff
1           2011-10-16 13:15:56     00:00:03
2           2011-10-16 13:15:59     00:00:00
3           2011-10-16 13:15:59     00:00:03
4           2011-10-16 13:16:02     00:02:05
5           2011-10-16 13:18:07     null

Demo on db-fiddle

Upvotes: 3

Jinav Shah
Jinav Shah

Reputation: 79

SELECT TIMESTAMPDIFF(SECOND, grd.startdate, grd1.startdate) as TD FROM myTable A 
inner join myTable B on A.requestId = B.requestId - 1 and 
A.startdate >= '2019-07-01' order by TD desc

Upvotes: 1

kalan nawarathne
kalan nawarathne

Reputation: 2014

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

Upvotes: 9

Yahia
Yahia

Reputation: 70369

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

Upvotes: 30

Related Questions