计算时间戳mysql之间的分钟差 [英] Calculate the difference in minutes between timestamp mysql

问题描述

我刚接触 mysql.我有一张这样的桌子:

hi I'm just new to mysql. i have a table like this:

TIME                |  USER        | interval
----------------------------------------------
2014-07-06 23:00:42 |     ngm      |  
----------------------------------------------
2014-07-06 23:01:33 |     ngm      | 
----------------------------------------------
2014-07-06 23:02:41 |     cpg      |  
----------------------------------------------
2014-07-06 23:03:48 |     ngm      |  
----------------------------------------------
2014-07-06 23:13:09 |     cpg      |  
----------------------------------------------
2014-07-06 23:18:31 |     cpg      |  
----------------------------------------------

我需要帮助计算用户"的时间"之间的分钟差异，因此它看起来像这样:

I need help in calculating the difference in minutes between 'time' by 'user' so it would look like this:

TIME                |  USER        | interval
----------------------------------------------
2014-07-06 23:00:42 |     ngm      | 0
----------------------------------------------
2014-07-06 23:01:33 |     ngm      | 1
----------------------------------------------
2014-07-06 23:02:41 |     cpg      | 0
----------------------------------------------
2014-07-06 23:03:48 |     ngm      | 2
----------------------------------------------
2014-07-06 23:13:09 |     cpg      | 11
----------------------------------------------
2014-07-06 23:18:31 |     cpg      | 5
----------------------------------------------

请帮忙.

推荐答案

 DROP TABLE IF EXISTS my_table;

 CREATE TABLE my_table
 (time DATETIME NOT NULL,user CHAR(3) NOT NULL,PRIMARY KEY(time,user));

 INSERT INTO my_table VALUES
 ('2014-07-06 23:00:42','ngm'),
 ('2014-07-06 23:01:33','ngm'),
 ('2014-07-06 23:02:41','cpg'),
 ('2014-07-06 23:03:48','ngm'),
 ('2014-07-06 23:13:09','cpg'),
 ('2014-07-06 23:18:31','cpg');

 mysql> SELECT * FROM my_table;
 +---------------------+------+
 | time                | user |
 +---------------------+------+
 | 2014-07-06 23:00:42 | ngm  |
 | 2014-07-06 23:01:33 | ngm  |
 | 2014-07-06 23:02:41 | cpg  |
 | 2014-07-06 23:03:48 | ngm  |
 | 2014-07-06 23:13:09 | cpg  |
 | 2014-07-06 23:18:31 | cpg  |
 +---------------------+------+

 SELECT x.*
      , COALESCE(
          SEC_TO_TIME(
            ROUND(TIME_TO_SEC(
              TIMEDIFF(x.time,MAX(y.time))
             )/60
            )*60
           ),0
          ) my_interval -- <-- can probably make shorter
   FROM my_table x 
   LEFT 
   JOIN my_table y 
     ON y.user = x.user 
    AND y.time < x.time 
  GROUP 
     BY time
      , user;
 +---------------------+------+-------------+
 | time                | user | my_interval |
 +---------------------+------+-------------+
 | 2014-07-06 23:00:42 | ngm  | 0           |
 | 2014-07-06 23:01:33 | ngm  | 00:01:00    |
 | 2014-07-06 23:02:41 | cpg  | 0           |
 | 2014-07-06 23:03:48 | ngm  | 00:02:00    |
 | 2014-07-06 23:13:09 | cpg  | 00:10:00    |
 | 2014-07-06 23:18:31 | cpg  | 00:05:00    |
 +---------------------+------+-------------+

这篇关于计算时间戳mysql之间的分钟差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！