CODEWITHSUNDEEP
mysql
Fran Rod
Fran Rod

Reputation: 596

Subtracting timestamps in MySQL

I have the following select statement where I subtract timestamps for knowing how long a truck has been stopped at a location:

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

The rows for calculating the 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 the select gives as result that there was a stop of 4 minutes for the vehicle 7466.

The problem comes when the rows are like 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 does not subtract. How can I get the resulting time?

Upvotes: 2

Views: 4395

Answers (3)

Tomas Creemers
Tomas Creemers

Reputation: 2715

I believe this is correct (see the SQL Fiddle to test it):

SELECT
  s1.id AS stopped_row_id, MIN(s2.id) AS departed_row_id, TIMESTAMPDIFF(SECOND, s1.timestamp, MIN(s2.timestamp)) AS stopped_seconds
FROM
  stops AS s1
  JOIN stops AS s2 ON
    s1.imei = s2.imei
    AND s1.id < s2.id
    AND (s1.timestamp + INTERVAL 4 MINUTE) <= s2.timestamp
  JOIN stops AS s3 ON
    s1.imei = s2.imei
    AND s3.id = s1.id - 1
  LEFT JOIN stops AS s4 ON
    s1.imei = s2.imei
    AND s4.id BETWEEN (s1.id + 1) AND (s2.id - 1)
    AND s4.speed <> 0
WHERE
  s1.speed = 0
  AND s2.speed <> 0
  AND s3.speed <> 0
  AND s4.id IS NULL
  AND s1.imei = 7466 -- optional; query will also give results for all imei if wanted
  AND s1.timestamp BETWEEN '2013-08-20 00:00:00' AND '2013-08-20 23:59:59' -- optional, query will give results for entire table if wanted
GROUP BY
  s1.id,
  s1.timestamp

In this query, s1 is the 'main' table.
s2 is joined to provide all rows with higher ids than that of s1 where the timestamp is at least 4 minutes higher than that of s1 (so in essence s2 is all rows indicating pauses long enough that the row from s1 should go into the final result set).
s3 is joined to make sure that the row in s1 is the first row where speed is 0 (for your example where there are sets of rows where speed is 0).
s4 is joined to make sure that there are no non-zero-speed rows between the 'selected' rows from s1 and s2.
The GROUP BY makes sure that we can get the first timestamp in the future where speed is not 0 by using MIN().

Apparently, the rows are not in a strict descending or ascending order (even if the IDs are). I have reworked the query to only use the time as 'sorting mechanism'. Note that this makes the query very slow, and you might be better off by ordering the table or a solution in the style of that of @peterm. Or at least adding an index on id andtimestamp.

SELECT
  s1.timestamp AS stopped_timestamp, MAX(s2.timestamp) as departed_timestamp, TIMESTAMPDIFF(SECOND, s1.timestamp, MAX(s2.timestamp)) AS stopped_seconds
FROM
  stops AS s1
  JOIN stops AS s2 ON
    s1.imei = s2.imei
    AND (s1.timestamp + INTERVAL 4 MINUTE) <= s2.timestamp
  JOIN stops AS s3 ON
    s1.imei = s2.imei
  LEFT JOIN stops AS s4 ON
    s1.imei = s2.imei
    AND s4.timestamp BETWEEN (s1.timestamp + INTERVAL 1 SECOND) AND (s2.timestamp - INTERVAL 1 SECOND)
    AND s4.speed <> 0
WHERE
  s1.speed = 0
  AND s2.speed <> 0
  AND s3.speed <> 0
  AND s4.id IS NULL
  AND s1.imei = 7466 -- optional; query will also give results for all imei if wanted
  AND s1.timestamp BETWEEN '2013-08-20 00:00:00' AND '2013-08-20 23:59:59' -- optional, query will give results for entire table if wanted
  AND s3.timestamp = (SELECT MAX(s5.timestamp) FROM stops AS s5 WHERE s5.timestamp < s1.timestamp)
GROUP BY
  s1.id,
  s1.timestamp

Upvotes: 1

peterm
peterm

Reputation: 92835

UPDATED One way to do it

SELECT duration / 60 duration
  FROM
(
  SELECT id, imei, timestamp, speed, 
         @d := IF(@s = 0 AND speed > 0, 
                  @d := @d + TIMESTAMPDIFF(SECOND, @t, timestamp),
                  @d) duration, 
         @t := IF(@s = 0 AND speed = 0, @t, timestamp),
         @s := speed 
    FROM stops, (SELECT @d := 0, @s := -1, @t = 0) i
   WHERE imei = 7466
     AND timestamp >= '2013-08-20 00:00:00' AND timestamp <= '2013-08-20 23:59:59' 
   ORDER BY timestamp
 ) z
 ORDER BY timestamp DESC
 LIMIT 1

Output:

+----------+
| duration |
+----------+
|        4 |
+----------+

Here is SQLFiddle demo

Upvotes: 0

Andy Jones
Andy Jones

Reputation: 6275

You're asking for a truck that is stopped for longer than 4 minutes (240 seconds). See the following line of your SQL query. 

hour(timediff(f2.timestamp,f.timestamp)) * 3600 + 
minute(timediff(f2.timestamp,f.timestamp)) * 60 + 
second(timediff(f2.timestamp,f.timestamp)) > 240

In the following table...

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

In none of these pairs are the trucks stopped for longer than 4 minutes (240 seconds). Because those pairs do NOT satisfy your WHERE conditions, they are not selected.

Upvotes: 0

Related Questions