I installed ha os into a new sd and I imported the old config directory from docker container to ha os… Works all but history of energy is lost.
Where is database of energy history? How can I import it?
Thanks.
Maybe I understood. There is a sqlite database into config called home_assistant_v2.db.
There are two interesting tables: statistics_meta and statistics. Maybe I can imports entries for this two tables and recover data. This evening I will try it.
I imported data from a DB to other with this python script:
import sqlite3
conOld = sqlite3.connect('pathOldDB\\home-assistant_v2.db')
conNew = sqlite3.connect('pathNewDB\\home-assistant_v2.db')
curOld = conOld.cursor();
curNew = conNew.cursor()
oldMap = {}
newMap = {}
for row in curOld.execute('select id, statistic_id from statistics_meta where id >= 2 and id <= 8'):
oldMap[row[1]] = row[0];
for row in curNew.execute('select id, statistic_id from statistics_meta where (id <= 19 and id >= 15) or (id >= 12 and id <= 13)'):
newMap[row[1]] = row[0];
print(oldMap)
print(newMap)
for row in curOld.execute('select SM.statistic_id, S.start, S.state, S.mean, S.min, S.max, S.last_reset, S.sum, S.created from statistics S inner join statistics_meta SM ON (S.metadata_id = SM.id ) where S.metadata_id >= 2 and S.metadata_id <= 8 order by S.start DESC'):
print(row)
curNew.execute("insert into statistics (metadata_id, start, state, mean, min, max, last_reset, sum, created) values (?, ?, ?, ?, ?, ?, ?, ?, ?)", (newMap[row[0]], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8],))
conNew.commit()
conOld.close();
conNew.close();
Works, but there is an error for only a single day. In 17/02/2022 at 22:00, ha shows me that I imported 2348 kWh, and I produced -1498kWh(negative value) solar energy. That’s impossibile.
These are data into db:
select SM.statistic_id, S.metadata_id, S.start, S.state, S.mean, S.min, S.max, S.last_reset, S.sum, S.created
from statistics S
inner join statistics_meta SM ON (S.metadata_id = SM.id )
where ((S.metadata_id <= 19 and S.metadata_id >= 15) or (S.metadata_id >= 12 and S.metadata_id <= 13)) AND S.start >= '2022-02-17 00:00:00.000000' AND S.start <= '2022-02-17 23:00:00.000000'
order by S.start DESC
"sensor.exported_energy_compensation" "19" "2022-02-17 23:00:00.000000" "0.0" "2022-02-17 23:50:53.434876" "0.0" "2022-02-18 00:00:10.228212"
"sensor.imported_energy_cost" "18" "2022-02-17 23:00:00.000000" "0.00659999999998035" "2022-02-17 23:50:53.433940" "0.089850000000024" "2022-02-18 00:00:10.227949"
"sensor.exported_energy" "17" "2022-02-17 23:00:00.000000" "538.718" "0.0" "2022-02-18 00:00:10.227696"
"sensor.imported_energy" "16" "2022-02-17 23:00:00.000000" "2349.125" "0.574000000000069" "2022-02-18 00:00:10.227432"
"sensor.solar_panels_energy" "15" "2022-02-17 23:00:00.000000" "1498.56" "0.0" "2022-02-18 00:00:10.227078"
"sensor.power_production_now_2" "13" "2022-02-17 23:00:00.000000" "0.0" "0.0" "0.0" "2022-02-18 00:00:10.226518"
"sensor.power_production_now" "12" "2022-02-17 23:00:00.000000" "0.0" "0.0" "0.0" "2022-02-18 00:00:10.226247"
"sensor.exported_energy_compensation" "19" "2022-02-17 22:00:00.000000" "0.0" "2022-02-17 22:58:06.970974" "0.0" "2022-02-17 23:00:35.140059"
"sensor.imported_energy_cost" "18" "2022-02-17 22:00:00.000000" "0.0" "2022-02-17 22:58:06.970241" "0.0343500000000403" "2022-02-17 23:00:35.139362"
"sensor.exported_energy" "17" "2022-02-17 22:00:00.000000" "538.718" "0.0" "2022-02-17 23:00:35.135642"
"sensor.imported_energy" "16" "2022-02-17 22:00:00.000000" "2348.755" "0.204000000000178" "2022-02-17 23:00:35.133864"
"sensor.solar_panels_energy" "15" "2022-02-17 22:00:00.000000" "1498.56" "0.0" "2022-02-17 23:00:35.132440"
"sensor.power_production_now_2" "13" "2022-02-17 22:00:00.000000" "0.0" "0.0" "0.0" "2022-02-17 23:00:35.130732"
"sensor.power_production_now" "12" "2022-02-17 22:00:00.000000" "0.0" "0.0" "0.0" "2022-02-17 23:00:35.124994"
"sensor.exported_energy_compensation" "19" "2022-02-17 21:00:00.000000" "0.0" "2022-02-17 21:57:55.310308" "0.0" "2022-02-17 22:00:10.302081"
"sensor.imported_energy_cost" "18" "2022-02-17 21:00:00.000000" "0.0" "2022-02-17 21:57:55.309651" "0.0" "2022-02-17 22:00:10.301831"
"sensor.exported_energy" "17" "2022-02-17 21:00:00.000000" "538.718" "0.0" "2022-02-17 22:00:10.301580"
"sensor.imported_energy" "16" "2022-02-17 21:00:00.000000" "2348.551" "0.0" "2022-02-17 22:00:10.301286"
"sensor.solar_panels_energy" "15" "2022-02-17 21:00:00.000000" "1498.56" "0.0" "2022-02-17 22:00:10.301040"
"sensor.power_production_now_2" "13" "2022-02-17 21:00:00.000000" "0.0" "0.0" "0.0" "2022-02-17 22:00:10.300545"
"sensor.power_production_now" "12" "2022-02-17 21:00:00.000000" "0.0" "0.0" "0.0" "2022-02-17 22:00:10.300292"
I also tried to delete all entries from 2022-02-17 to now, but same error goes into new bar of chart.