I updated @monstrenyatko code to match Home Assistant 2025.09 schema (just two added fields: c.mean_type and c.mean_weight):
-- insert possibly missing statistics_meta records
insert into statistics_meta
select row_number() over () + (select max(id) from statistics_meta) as id, c.statistic_id, c.source, c.unit_of_measurement, c.has_mean, c.has_sum, c.name, c.mean_type
from corrupt.statistics_meta c
left outer join statistics_meta m on m.statistic_id = c.statistic_id
where m.id is null;
-- move existing ids, as all corrupt records are older
update statistics set id = - (id + (select max(id) from corrupt.statistics)) where id > 0;
update statistics set id = - id where id < 0;
-- import statistics from corrupt db
insert or ignore into statistics
select c.id, c.created, c.created_ts, m.new_id as metadata_id, c.start, c.start_ts, c.mean, c.mean_weight, c.min, c.max, c.last_reset, c.last_reset_ts, c.state, c.sum
from corrupt.statistics c
inner join (
select o.id as old_id
, n.id as new_id
, case when row_number() over (partition by o.id) > 1 or row_number() over (partition by n.id) > 1 then 0 else 1 end as test
from corrupt.statistics_meta o
inner join statistics_meta n on n.statistic_id = o.statistic_id
) m on m.old_id = c.metadata_id and m.test = 1 order by id;
-- update sum values causing mega negative values for day of corruption in Energy Dashboard
update statistics
set sum = sum + a.add_sum
from (
select s.id, a.statistic_id, sum(a.lag_sum) as add_sum
from statistics s
inner join (
select *
from (
select m.statistic_id, s.*, lag(s.sum) over (partition by s.metadata_id order by s.start_ts) as lag_sum
from statistics s
inner join statistics_meta m on m.id = s.metadata_id
where s.sum is not null
) t
where t.sum < t.lag_sum
) a on a.metadata_id = s.metadata_id and s.start_ts >= a.start_ts
group by s.id, a.statistic_id
) a
where a.id = statistics.id;
-- rebuild statistics_short_term
delete from statistics_short_term;
insert into statistics_short_term
select row_number() over () as id, c.created, c.created_ts, c.metadata_id, c.start, c.start_ts, c.mean, c.mean_weight, c.min, c.max, c.last_reset, c.last_reset_ts, c.state, c.sum
from statistics c
where date(c.start_ts, 'unixepoch') = DATE('now')
order by c.start_ts, c.id;
I had to stop HA, save the current database elsewhere, and start HA with the corrupted database in order to have its schema updated to the latest version. Then I stopped HA, saved the updated corrupted database, open the current database (home-assistant_v2.db) file and then attach the updated corrupted database (home-assistant_v2.db.corrupt) as "corrupt" (it asks for the name when you select the file). Finally, execute the script above and press the "Write Changes" button. At this point, the current database (home-assistant_v2.db) file can be used by HA with all the history recovered.
Of course, I expect the script above to require new changes as HA evolves over time and the schema changes.
