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

Question

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.

Answer 1

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

• Rep: Thank you. I tried it and it worked. But i wonder, is this creating a new table with reduced memory needed? Because that is the purpose of my question, i have to much data and it consumes to much memory.