本文介绍了计算时间戳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之间的分钟差的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!