Fix energy statistic, starting with offset

Hey,

I need help to fix a broken energy statistic. My sensor reports the current reading from my smartmeter. But that means, when it was first measured it returned like 1000 kwH. So in the energy statistic there shows a 1000 kwH Peak in the first hour the sensor was tracked.

I checked the statistics-table and indeed, the wrong statistic is the first entry for this sensor:

Using the Developer Tools to fix the statistic worked for another day with a bad reading, but obviously not for the first statistic of a sensor:

So, how do I fix this? :slight_smile:

FYI, I had to fix some data myself recently. I found the webtool not very praticle and suggested adding phpliteadmin to phpmyadmin but it’s considered “unmaintained” and I could not wait for it anyway.

So I made the following bash script executing the SQL(ite) commands to modify the data.
I used the Web UI and the select statements to get a better idea how I wanted to modify the data (I essentially wanted to undo some mess I made with recent values due to development).
Then I added the UPDATE commands.

I found that the “statistics” table is what is actually used in the energy tab, but assumed that “statistics_short_term” which resembles a lot would likely also be recommended to update.

I hope this can inspire you.

#!/bin/bash
# 50, 51, 55

# Do update of gas_consumption_kwh
# metadata_id can be found in metadata table
#CREATE TABLE statistics_meta  (
#  id INTEGER NOT NULL,
#    statistic_id VARCHAR(255),
#      source VARCHAR(32),
#        unit_of_measurement VARCHAR(255),
#         has_mean BOOLEAN,
#           has_sum BOOLEAN,
#             name VARCHAR(255),
#               PRIMARY KEY (id)
#               )

#mean,min,max,sum
#10727509|2023-01-24 20:30:11.186135|2023-01-24 20:25:00.000000|28657.0|28657.0|28657.0||||50


sqlite3 home-assistant_v2.db <<'EOSQL'
update "statistics_short_term"
set mean=null,min=null,max=null,state=29305,sum=33752+29305-28657
where metadata_id in (50)
AND id>=10726675
;
EOSQL

#id|created|start|mean|min|max|last_reset|state|sum|metadata_id
#10694884|2023-01-24 00:50:17.716574|2023-01-24 00:45:00.000000|28657.0|28657.0|28657.0||||50
#10694745|2023-01-24 00:45:31.049645|2023-01-24 00:40:00.000000|||||28657.0|33752.0|50

#10726675|2023-01-24 20:00:11.205636|2023-01-24 19:55:00.000000|||||28657.0|33752.0|50
#922191|2023-01-24 20:00:11.472642|50|2023-01-24 19:00:00.000000|||||28657.0|33752.0

sqlite3 home-assistant_v2.db <<'EOSQL'
.headers on
SELECT *
FROM "statistics_short_term"
where metadata_id in (50)
AND id>=10727509
order by id desc;
EOSQL

sqlite3 home-assistant_v2.db <<'EOSQL'
update "statistics"
set mean=null,min=null,max=null,state=29305,sum=33752+29305-28657
where metadata_id in (50)
AND id>=922191
;
EOSQL

sqlite3 home-assistant_v2.db <<'EOSQL'
.headers on
SELECT *
FROM "statistics"
where metadata_id in (50)
AND id<=10727509
order by id desc;
EOSQL

Thanks a lot, le_top. But I don’t know what i should set state and sum to. From what I understand the consumption in energy dashboard is calculcated by the diff of two entries in the statistic-table. So when I reduce the sum by 1000 in the first entry, the wrong value will just appear one hour later? And I can’t reduce all entries because then any new entry would be wrong?

As far as I understood, ‘state’ is the current value of the meter.
‘sum’ is the accumulated value sins the beginning.

When a meter drops more than 10% it is considered to be reset and it will use the new value as a base line. So as far as I understand you should adjust the sum and the meter by the same amounts, and in both tables.