;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY accountID, deviceID ORDER BY timestamp) AS Seq,*
FROM YourTable
)

SELECT t.accountID, t.deviceID, 
t.[timestamp] AS from_timestamp,
t1.nexttimestamp AS to_timestamp
t1.nexttimestamp - t.[timestamp] AS diff
FROM CTE t
OUTER APPLY (SELECT MIN([timestamp]) AS nexttimestamp
             FROM CTE
             WHERE accountID = t.accountID 
             AND deviceID = t.deviceID
             AND Seq > t.Seq
             AND speedKPH <> 0
            )t1
WHERE t.speedKPH = 0
AND NOT EXISTS (SELECT 1
FROM CTE
WHERE accountID = t.accountID 
AND deviceID = t.deviceID
AND Seq = t.Seq -1
AND speedKPH = 0)

quote:

---

Originally posted by visakh16

Something like this if in sql server (dont know about mysql)

;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY accountID, deviceID ORDER BY timestamp) AS Seq,*
FROM YourTable
)

SELECT t.accountID, t.deviceID, 
t.[timestamp] AS from_timestamp,
t1.nexttimestamp AS to_timestamp
t1.nexttimestamp - t.[timestamp] AS diff
FROM CTE t
OUTER APPLY (SELECT MIN([timestamp]) AS nexttimestamp
             FROM CTE
             WHERE accountID = t.accountID 
             AND deviceID = t.deviceID
             AND Seq > t.Seq
             AND speedKPH <> 0
            )t1
WHERE t.speedKPH = 0
AND NOT EXISTS (SELECT 1
FROM CTE
WHERE accountID = t.accountID 
AND deviceID = t.deviceID
AND Seq = t.Seq -1
AND speedKPH = 0)

Try this and if it doesnt work please post in some mysql forums for more help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

---