· 6 years ago · Jul 25, 2019, 12:06 PM
1TIME | USER | interval
2----------------------------------------------
32014-07-06 23:00:42 | ngm |
4----------------------------------------------
52014-07-06 23:01:33 | ngm |
6----------------------------------------------
72014-07-06 23:02:41 | cpg |
8----------------------------------------------
92014-07-06 23:03:48 | ngm |
10----------------------------------------------
112014-07-06 23:13:09 | cpg |
12----------------------------------------------
132014-07-06 23:18:31 | cpg |
14----------------------------------------------
15
16TIME | USER | interval
17----------------------------------------------
182014-07-06 23:00:42 | ngm | 0
19----------------------------------------------
202014-07-06 23:01:33 | ngm | 1
21----------------------------------------------
222014-07-06 23:02:41 | cpg | 0
23----------------------------------------------
242014-07-06 23:03:48 | ngm | 2
25----------------------------------------------
262014-07-06 23:13:09 | cpg | 11
27----------------------------------------------
282014-07-06 23:18:31 | cpg | 5
29----------------------------------------------
30
31DROP TABLE IF EXISTS my_table;
32
33 CREATE TABLE my_table
34 (time DATETIME NOT NULL,user CHAR(3) NOT NULL,PRIMARY KEY(time,user));
35
36 INSERT INTO my_table VALUES
37 ('2014-07-06 23:00:42','ngm'),
38 ('2014-07-06 23:01:33','ngm'),
39 ('2014-07-06 23:02:41','cpg'),
40 ('2014-07-06 23:03:48','ngm'),
41 ('2014-07-06 23:13:09','cpg'),
42 ('2014-07-06 23:18:31','cpg');
43
44 mysql> SELECT * FROM my_table;
45 +---------------------+------+
46 | time | user |
47 +---------------------+------+
48 | 2014-07-06 23:00:42 | ngm |
49 | 2014-07-06 23:01:33 | ngm |
50 | 2014-07-06 23:02:41 | cpg |
51 | 2014-07-06 23:03:48 | ngm |
52 | 2014-07-06 23:13:09 | cpg |
53 | 2014-07-06 23:18:31 | cpg |
54 +---------------------+------+
55
56 SELECT x.*
57 , COALESCE(
58 SEC_TO_TIME(
59 ROUND(TIME_TO_SEC(
60 TIMEDIFF(x.time,MAX(y.time))
61 )/60
62 )*60
63 ),0
64 ) my_interval -- <-- can probably make shorter
65 FROM my_table x
66 LEFT
67 JOIN my_table y
68 ON y.user = x.user
69 AND y.time < x.time
70 GROUP
71 BY time
72 , user;
73 +---------------------+------+-------------+
74 | time | user | my_interval |
75 +---------------------+------+-------------+
76 | 2014-07-06 23:00:42 | ngm | 0 |
77 | 2014-07-06 23:01:33 | ngm | 00:01:00 |
78 | 2014-07-06 23:02:41 | cpg | 0 |
79 | 2014-07-06 23:03:48 | ngm | 00:02:00 |
80 | 2014-07-06 23:13:09 | cpg | 00:10:00 |
81 | 2014-07-06 23:18:31 | cpg | 00:05:00 |
82 +---------------------+------+-------------+