Choose the difference between two 2 line unix timestamps

Question

I have a table in MySQL with start and stop times.

I want to train time from a stop to the next start.

I did sqlfiddle with my table http://sqlfiddle.com/#!2/2c043/1

ID start_time end_time 410 1367894268 1367898275 409 1367893164 1367894268 408 1367888257 1367893153 407 1367837969 1367888247 

so i would like to do

 end_time from row 410 - the start_time from row 409 1367898275 - 1367893164 

and output as

 end_time start_time time_duration 1367898275 1367893164 5111 

Thank you for your help.

Answer 1

TRY BELOW, IT WILL HELP YOU

SQL FIDDLE: http://sqlfiddle.com/#!2/2c043/26

 SELECT A.idtrip_data, A.start_time, B.end_time, (B.end_time - a.start_time) AS timedifference FROM myTable A INNER JOIN myTable B ON B.idtrip_data = (A.idtrip_data + 1) ORDER BY A.idtrip_data ASC 

Answer 2

To achieve what you ask, try the following:

 SELECT (B.start_time - A.end_time) AS time_duration FROM MyTable A INNER JOIN MyTable B ON B.ID = (A.ID + 1) ORDER BY A.ID ASC 

IF ID not sequential, then you can use

 SELECT (B.start_time - A.end_time) AS time_duration FROM MyTable A CROSS JOIN MyTable B WHERE B.ID IN (SELECT MIN (C.ID) FROM MyTable C WHERE C.ID > A.ID) ORDER BY A.ID ASC