Our home battery measures different sensor data, such as:
- Current energy consumption in watts
- Current energy production (by photovoltaic) in watts
- Amount of watts that was fed into the grid
I collect this data every second via a python script and insert it into a mysql table. Because that data sums up over the years (Multiple Gigabytes!), i wanted to compress the data to minute averages for the old data.
Table schema
Sensor Table:
create table status_sensor
(
# timestamp with second precision
measured_at timestamp not null,
# e.g. PRODUCTION for PV-Energy produced in that second
type varchar(30) not null,
# numeric value in watts
measurement int not null,
primary key (measured_at, type)
);
Table for compressed data:
create table status_compressed
(
# timestamp with minute precision
measured_at timestamp not null,
# e.g. PRODUCTION for PV-Energy produced in that second
type varchar(30) not null,
# Average watt value in that minute
measurement int not null,
primary key (measured_at, type)
);
Compact Algorithm
Compact can be performed directly via sql. No programming necessary.
High-Level Steps:
- Get entries grouped by
type
andmeasured_at
minute from the tablestatus_sensor
for a specific year. - Insert them to the
status_compressed
table - Delete the sensor entries from
status_sensor
for that specific year
SQLs:
1+2:
INSERT INTO status_compressed (measured_at, type, measurement)
SELECT SUBSTRING_INDEX(entry, '/', 1) AS date,
SUBSTRING_INDEX(entry, '/', -1) AS type,
average
FROM (SELECT CONCAT(DATE_FORMAT(measured_at, '%Y-%m-%d %H:%i'), '/', type) entry, avg(measurement) average
FROM status_sensor
WHERE measured_at LIKE '2020%'
GROUP BY CONCAT(DATE_FORMAT(measured_at, '%Y-%m-%d %H:%i'), '/', type)) sub
3:
DELETE FROM status_sensor WHERE measured_at LIKE '2020%';
Top comments (0)