如何在MySQL中计算当前行与上一行之间的时差 [英] How to calculate time difference between current and previous row in MySQL

问题描述

我有这样的mysql表t1:

I have mysql table t1 like this :

我想做的是在所有行之间进行计算，并将值保存在名为diff的新列中

What i want to do is do calculations between all rows and save the value in new coloumn called diff

TICKETID| DATENEW  | DIFF
16743     12:36:46   0
16744     12:51:25   15.  minute  
16745     12:57:25   6.5  minute
..........
.......
etc

我知道也有类似的问题，但我尝试了所有解决方案 在这里没有成功发布，那么如何解决这个查询呢?

i know there are similar questions ,but ive tried all of the solutions posted here with no success,so how to solve this query ???

推荐答案

要获取当前行和上一行之间的时差(以分钟为单位)，可以在datenow上使用timestampdiff并使用上一时间.通过子查询:

To get the time difference in minutes between the current and previous row, you can use timestampdiff on datenow and the previous time, which you can get via subquery:

select ticketid, datenew,
    timestampdiff(minute,datenew,(select datenew from mytable t2
        where t2.ticketid < t1.ticketid order by t2.ticketid desc limit 1)) as diff
from mytable t1

更新

这是使用变量存储以前的datenew值的另一种方法，该值可能会更快:

Here's another way using a variable to store the previous datenew value that might be faster:

select ticketid, datenew, timestampdiff(minute,datenew,prevdatenew)
from (
    select ticketid, datenew, @prevDateNew as prevdatenew, 
      @prevDateNew := datenew
    from mytable order by ticketid
) t1

这篇关于如何在MySQL中计算当前行与上一行之间的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！