Mysql – How to reduce redundant time-series data in MySQL into a function

MySQLoptimizationtime

i have a question that actually does not fit here very much, but maybe it fits. So, i have data like this:

enter image description here

How can i reduce this 3 entries with same values? Sure, i can just delete the other 2, but in time-series data i can not just do this, this would hide the information, that in between the real time 15:19:45 and 15:19:55, the value did not change. So i thought about saving the delta-time and value in a separate table and only save the first entry and mark it, but i dont know if this is the best way to do so.

Best Answer

YOUR SAMPLE DATA

DROP DATABASE IF EXISTS rep;
CREATE DATABASE rep;
USE rep
CREATE TABLE timeseries
(
    id             INT NOT NULL AUTO_INCREMENT,
    polltime       DATETIME NOT NULL,
    lastupdatetime DATETIME NOT NULL,
    name           VARCHAR(20) NOT NULL,
    value          VARCHAR(20) NOT NULL,
    PRIMARY KEY    (id),
    KEY series_ndx (lastupdatetime,name,value,polltime)
);
INSERT INTO timeseries
(polltime,lastupdatetime,name,value) VALUES
('2016-09-09 15:19:45','2016-09-09 15:19:44','x','15,2'),
('2016-09-09 15:19:50','2016-09-09 15:19:44','x','15,2'),
('2016-09-09 15:19:55','2016-09-09 15:19:44','x','15,2'),
('2016-09-09 15:20:00','2016-09-09 15:19:56','y','16,05'),
('2016-09-09 15:20:05','2016-09-09 15:19:58','c','16,12'),
('2016-09-09 15:20:10','2016-09-09 15:20:12','x','15,98');
SELECT * FROM timeseries;

PROPOSED QUERY

SELECT SEC_TO_TIME(MAX(polltime)-MIN(polltime)) timediff,lastupdatetime,name,value
FROM timeseries GROUP BY lastupdatetime,name,value;

PROPOSED QUERY EXECUTED

mysql> SELECT SEC_TO_TIME(MAX(polltime)-MIN(polltime)) timediff,lastupdatetime,name,value
    -> FROM timeseries GROUP BY lastupdatetime,name,value;
+----------+---------------------+------+-------+
| timediff | lastupdatetime      | name | value |
+----------+---------------------+------+-------+
| 00:00:10 | 2016-09-09 15:19:44 | x    | 15,2  |
| 00:00:00 | 2016-09-09 15:19:56 | y    | 16,05 |
| 00:00:00 | 2016-09-09 15:19:58 | c    | 16,12 |
| 00:00:00 | 2016-09-09 15:20:12 | x    | 15,98 |
+----------+---------------------+------+-------+
4 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

Please note that you will have to index this table to make the aggregation fast

Related Question