mysql - mysql中每行的日期差异

Question

我想得到每个日期时间行的秒数差异。我怎样才能在mysql中实现这一点？

id record
1 2019-02-12 19:59:44
2 2019-02-12 20:00:27
3 2019-02-12 20:01:10

期望的输出:

id record               difference in seconds
1  2019-02-12 19:59:44  0
2  2019-02-12 20:00:27  43
3  2019-02-12 20:01:10  44

Answer 1

我认为最简单的方法是转换为可以直接计算的UNIX_TIMESTAMP()。

您可以直接在当前记录和 LAG() 记录之间进行计算。

列 - LAG(列) OVER()
列 + LAG(列) OVER()

查询

SELECT 
   t.id
 , t.record
 , (
     CASE 
       WHEN 
          UNIX_TIMESTAMP(t.record) - UNIX_TIMESTAMP(LAG(t.record) OVER(ORDER BY t.record ASC)) IS NOT NULL 
       THEN 
          UNIX_TIMESTAMP(t.record) - UNIX_TIMESTAMP(LAG(t.record) OVER(ORDER BY t.record ASC))
       ELSE 0
     END
   ) AS difference_in_seconds                                                                      
FROM 
 t
ORDER BY 
  t.id ASC

结果

| id  | record              | difference_in_seconds |
| --- | ------------------- | --------------------- |
| 1   | 2019-02-12 19:59:44 | 0                     |
| 2   | 2019-02-12 20:00:27 | 43                    |
| 3   | 2019-02-12 20:01:10 | 43                    |

参见demo

Why repeating yourself in the CASE expression? Use COALESCE

确实

COALESCE(
       UNIX_TIMESTAMP(t.record) - UNIX_TIMESTAMP(LAG(t.record) OVER(ORDER BY t.record ASC))
     , 0                                                                  
   ) AS difference_in_seconds  

与使用相同

 (
     CASE 
       WHEN 
          UNIX_TIMESTAMP(t.record) - UNIX_TIMESTAMP(LAG(t.record) OVER(ORDER BY t.record ASC)) IS NOT NULL 
       THEN 
          UNIX_TIMESTAMP(t.record) - UNIX_TIMESTAMP(LAG(t.record) OVER(ORDER BY t.record ASC))
       ELSE 0
     END
   ) AS difference_in_seconds