CDgpbRfN

1DROP DATABASE IF EXISTS rep;
2CREATE DATABASE rep;
3USE rep
4CREATE TABLE timeseries
5(
6    id             INT NOT NULL AUTO_INCREMENT,
7    polltime       DATETIME NOT NULL,
8    lastupdatetime DATETIME NOT NULL,
9    name           VARCHAR(20) NOT NULL,
10    value          VARCHAR(20) NOT NULL,
11    PRIMARY KEY    (id),
12    KEY series_ndx (lastupdatetime,name,value,polltime)
13);
14INSERT INTO timeseries
15(polltime,lastupdatetime,name,value) VALUES
16('2016-09-09 15:19:45','2016-09-09 15:19:44','x','15,2'),
17('2016-09-09 15:19:50','2016-09-09 15:19:44','x','15,2'),
18('2016-09-09 15:19:55','2016-09-09 15:19:44','x','15,2'),
19('2016-09-09 15:20:00','2016-09-09 15:19:56','y','16,05'),
20('2016-09-09 15:20:05','2016-09-09 15:19:58','c','16,12'),
21('2016-09-09 15:20:10','2016-09-09 15:20:12','x','15,98');
22SELECT * FROM timeseries;
23	
24SELECT SEC_TO_TIME(MAX(polltime)-MIN(polltime)) timediff,lastupdatetime,name,value
25FROM timeseries GROUP BY lastupdatetime,name,value;
26	
27mysql> SELECT SEC_TO_TIME(MAX(polltime)-MIN(polltime)) timediff,lastupdatetime,name,value
28    -> FROM timeseries GROUP BY lastupdatetime,name,value;
29+----------+---------------------+------+-------+
30| timediff | lastupdatetime      | name | value |
31+----------+---------------------+------+-------+
32| 00:00:10 | 2016-09-09 15:19:44 | x    | 15,2  |
33| 00:00:00 | 2016-09-09 15:19:56 | y    | 16,05 |
34| 00:00:00 | 2016-09-09 15:19:58 | c    | 16,12 |
35| 00:00:00 | 2016-09-09 15:20:12 | x    | 15,98 |
36+----------+---------------------+------+-------+
374 rows in set (0.00 sec)
38
39mysql>