Subtracting timestamps in MySQL -

i have following select statement subtract timestamps knowing how long truck has been stopped @ location:

 select  f.id, f.imei imei, f.speed speed  stops f, stops f2  f2.id = f.id-1  , f.imei = 7466  , hour(timediff(f2.timestamp,f.timestamp)) * 3600 +   minute(timediff(f2.timestamp,f.timestamp)) * 60 + second(timediff(f2.timestamp,f.timestamp)) > 240   , f.speed = 0  , f2.timestamp >= '2013-08-20 00:00:00'   , f2.timestamp <= '2013-08-20 23:59:59'    order f2.timestamp desc 

the rows calculating stops:

id imei     timestamp               speed 1  7466     2013-08-20 13:19:00     30     2  7466     2013-08-20 13:20:00     0  3  7466     2013-08-20 13:24:30     20 

so select gives result there stop of 4 minutes vehicle 7466.

the problem comes when rows this:

id imei     timestamp               speed  1  7466     2013-08-20 13:19:00     30      2  7466     2013-08-20 13:20:00     0  3  7466     2013-08-20 13:21:00     0  4  7466     2013-08-20 13:22:00     0  5  7466     2013-08-20 13:24:30     20 

it not subtract. how can resulting time?

i believe correct (see sql fiddle test it):

select   s1.id stopped_row_id, min(s2.id) departed_row_id, timestampdiff(second, s1.timestamp, min(s2.timestamp)) stopped_seconds   stops s1   join stops s2 on     s1.imei = s2.imei     , s1.id < s2.id     , (s1.timestamp + interval 4 minute) <= s2.timestamp   join stops s3 on     s1.imei = s2.imei     , s3.id = s1.id - 1   left join stops s4 on     s1.imei = s2.imei     , s4.id between (s1.id + 1) , (s2.id - 1)     , s4.speed <> 0   s1.speed = 0   , s2.speed <> 0   , s3.speed <> 0   , s4.id null   , s1.imei = 7466 -- optional; query give results imei if wanted   , s1.timestamp between '2013-08-20 00:00:00' , '2013-08-20 23:59:59' -- optional, query give results entire table if wanted group   s1.id,   s1.timestamp 

in query, s1 'main' table.
s2 joined provide rows higher ids of s1 timestamp @ least 4 minutes higher of s1 (so in essence s2 rows indicating pauses long enough row s1 should go final result set).
s3 joined make sure row in s1 first row speed 0 (for example there sets of rows speed 0).
s4 joined make sure there no non-zero-speed rows between 'selected' rows s1 , s2.
group by makes sure can first timestamp in future speed not 0 using min().

---

apparently, rows not in strict descending or ascending order (even if ids are). have reworked query use time 'sorting mechanism'. note makes query slow, , might better off ordering table or solution in style of of @peterm. or @ least adding index on id andtimestamp.

select   s1.timestamp stopped_timestamp, max(s2.timestamp) departed_timestamp, timestampdiff(second, s1.timestamp, max(s2.timestamp)) stopped_seconds   stops s1   join stops s2 on     s1.imei = s2.imei     , (s1.timestamp + interval 4 minute) <= s2.timestamp   join stops s3 on     s1.imei = s2.imei   left join stops s4 on     s1.imei = s2.imei     , s4.timestamp between (s1.timestamp + interval 1 second) , (s2.timestamp - interval 1 second)     , s4.speed <> 0   s1.speed = 0   , s2.speed <> 0   , s3.speed <> 0   , s4.id null   , s1.imei = 7466 -- optional; query give results imei if wanted   , s1.timestamp between '2013-08-20 00:00:00' , '2013-08-20 23:59:59' -- optional, query give results entire table if wanted   , s3.timestamp = (select max(s5.timestamp) stops s5 s5.timestamp < s1.timestamp) group   s1.id,   s1.timestamp