MySql: Compact sensor data to minute averages

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:

1. Get entries grouped by type and measured_at minute from the table status_sensor for a specific year.
2. Insert them to the status_compressed table
3. 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%';