mysql - 在 MySQL 中减去时间戳

Question

我有以下选择语句，其中减去时间戳以了解卡车在某个位置停了多长时间:

 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

计算止损的行:

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

所以选择给出的结果是车辆 7466 停了 4 分钟。

当行是这样的时候问题就来了:

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

它不减法。我怎样才能得到结果时间？

Answer 1

我相信这是正确的(请参阅 SQL Fiddle 进行测试):

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

在此查询中，s1 是“主”表。
s2 被加入以提供具有比 s1 更高的 id 的所有行，其中 timestamp 至少为 4分钟比 s1 高(因此本质上 s2 是所有指示暂停时间足够长的行，因此来自 s1 的行应该进入最终结果设置)。
加入 s3 以确保 s1 中的行是 first 行，其中 speed 为 0(对于您的例如，存在 speed 为 0 的行集。
加入 s4 以确保在 s1 和 s2 的“选定”行之间没有非零速度行。
GROUP BY 确保我们可以通过使用 MIN().

---

显然，这些行并不是严格按照降序或升序排列的(即使 ID 是这样)。我修改了查询以仅将时间用作“排序机制”。请注意，这会使查询非常慢，您可能会通过以 @peterm 的样式订购表或解决方案来获得更好的效果。或者至少在 id 和 timestamp 上添加索引。

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