Hi,
I reinstalled the MariaDB addon after a lot of errors. Everything worked again after the new database was installed and I can see my energy dashboard.
I have a dump of the old database. I managed to get the energy statistics from it (like only the sensors udes in the dashboard). With this SQL:
SELECT * FROM HomeAssistant.statistics WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.energy_day_fronius_inverter_1_192_168_74_225');
After viewing the results I inserted these results into a ‘temp’ database (temp_fronius in this example).
Now everything in 2022 is back, I can see my historical data again. BUT… In februari of this year all results from 2022 are in the statistics. I believe this is due to the sum column. It now has some ‘false’ data. But how can I correct this?
All the SQL lines I used in my database manipulation:
# Creating new temporary tables:
create table temp_fronius like statistics;
create table temp_energy_cons1 like statistics;
create table temp_energy_cons2 like statistics;
create table temp_energy_prod1 like statistics;
create table temp_energy_prod2 like statistics;
# Getting the data I need (change the name of the sensor accordingly)
SELECT * FROM HomeAssistant.statistics WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.energy_day_fronius_inverter_1_192_168_74_225');
#In the old database the fronius production is in kWh, in the new in Wh.. So multiply everything:
update temp_fronius SET state = (state * 1000) where metadata_id = 54;
# Correcting the metadata_ID's
update temp_energy_cons1 SET metadata_id = 21 where metadata_id = 3;
update temp_energy_cons2 SET metadata_id = 22 where metadata_id = 4;
update temp_energy_prod1 SET metadata_id = 23 where metadata_id = 5;
update temp_energy_prod2 SET metadata_id = 24 where metadata_id = 6;
update temp_fronius SET metadata_id = 54 where metadata_id = 58;
# Put all the data in the temp databases into the production database
insert into statistics (created,start,mean,min,max,last_reset,state,sum,metadata_id,created_ts,start_ts,last_reset_ts) SELECT temp_energy_cons1.created,temp_energy_cons1.start,temp_energy_cons1.mean,temp_energy_cons1.min,temp_energy_cons1.max,temp_energy_cons1.last_reset,temp_energy_cons1.state,temp_energy_cons1.sum,temp_energy_cons1.metadata_id,temp_energy_cons1.created_ts,temp_energy_cons1.start_ts,temp_energy_cons1.last_reset_ts from temp_energy_cons1;
insert into statistics (created,start,mean,min,max,last_reset,state,sum,metadata_id,created_ts,start_ts,last_reset_ts) SELECT temp_energy_cons2.created,temp_energy_cons2.start,temp_energy_cons2.mean,temp_energy_cons2.min,temp_energy_cons2.max,temp_energy_cons2.last_reset,temp_energy_cons2.state,temp_energy_cons2.sum,temp_energy_cons2.metadata_id,temp_energy_cons2.created_ts,temp_energy_cons2.start_ts,temp_energy_cons2.last_reset_ts from temp_energy_cons2;
insert into statistics (created,start,mean,min,max,last_reset,state,sum,metadata_id,created_ts,start_ts,last_reset_ts) SELECT temp_energy_prod1.created,temp_energy_prod1.start,temp_energy_prod1.mean,temp_energy_prod1.min,temp_energy_prod1.max,temp_energy_prod1.last_reset,temp_energy_prod1.state,temp_energy_prod1.sum,temp_energy_prod1.metadata_id,temp_energy_prod1.created_ts,temp_energy_prod1.start_ts,temp_energy_prod1.last_reset_ts from temp_energy_prod1;
insert into statistics (created,start,mean,min,max,last_reset,state,sum,metadata_id,created_ts,start_ts,last_reset_ts) SELECT temp_energy_prod2.created,temp_energy_prod2.start,temp_energy_prod2.mean,temp_energy_prod2.min,temp_energy_prod2.max,temp_energy_prod2.last_reset,temp_energy_prod2.state,temp_energy_prod2.sum,temp_energy_prod2.metadata_id,temp_energy_prod2.created_ts,temp_energy_prod2.start_ts,temp_energy_prod2.last_reset_ts from temp_energy_prod2;
insert into statistics (created,start,mean,min,max,last_reset,state,sum,metadata_id,created_ts,start_ts,last_reset_ts) SELECT temp_fronius.created,temp_fronius.start,temp_fronius.mean,temp_fronius.min,temp_fronius.max,temp_fronius.last_reset,temp_fronius.state,temp_fronius.sum,temp_fronius.metadata_id,temp_fronius.created_ts,temp_fronius.start_ts,temp_fronius.last_reset_ts from temp_fronius;
As you can see I don’t know much about SQL. This was a learning experience.
Does anybody know how to fix the quirk in my database?