Hi,
During my energy monitor setup (using esphome) it has triggered a wrong data. I don’t know how, because now everything looks fine. Do you know how I can remove it ? This 20K kWh trigged is masking the real value when in the same view.
I’m using mariadb and tried to search for this value in the db but cannot find it…
I recently fumbled my way through this - I made a mistake pulling data from an rtlamr influx bucket. I’m using sqlite, but the principal should be similar.
You should probably practice this on a copy of your db 
First lookup your sensor’s details in the statistics_meta table:
sqlite> select * from statistics_meta where statistic_id = "sensor.electric_meter_reading";
id|statistic_id|source|unit_of_measurement|has_mean|has_sum|name
53|sensor.electric_meter_reading|recorder|kWh|0|1|
Substitute the right sensor name for your installation. This tells me my electric sensor has id = 53.
Second, find the senor data in the statistics table. I’m going to filter by date - I know the original data was entered on Jan 17.
sqlite> select * from statistics where metadata_id=53 and created < '2022-01-18' order by created asc limit 20;
id|created|start|mean|min|max|last_reset|state|sum|metadata_id
57407|2022-01-17 08:00:10.084262|2022-01-17 07:00:00.000000||||1970-01-01 00:00:00.000000|765.9|0.399999999999977|53
57459|2022-01-17 09:00:10.083756|2022-01-17 08:00:00.000000||||1970-01-01 00:00:00.000000|766.7|1.20000000000005|53
57513|2022-01-17 10:00:10.085860|2022-01-17 09:00:00.000000||||1970-01-01 00:00:00.000000|767.5|2.0|53
57567|2022-01-17 11:00:10.076517|2022-01-17 10:00:00.000000||||1970-01-01 00:00:00.000000|768.3|2.79999999999995|53
57621|2022-01-17 12:00:10.076196|2022-01-17 11:00:00.000000||||1970-01-01 00:00:00.000000|769.1|3.60000000000002|53
57675|2022-01-17 13:00:10.079523|2022-01-17 12:00:00.000000||||1970-01-01 00:00:00.000000|770.0|4.5|53
57729|2022-01-17 14:00:10.075745|2022-01-17 13:00:00.000000||||1970-01-01 00:00:00.000000|770.8|5.29999999999995|53
57783|2022-01-17 15:00:10.079669|2022-01-17 14:00:00.000000||||1970-01-01 00:00:00.000000|771.7|6.20000000000005|53
57837|2022-01-17 16:00:10.056909|2022-01-17 15:00:00.000000||||1970-01-01 00:00:00.000000|772.5|7.0|53
57891|2022-01-17 17:00:10.065551|2022-01-17 16:00:00.000000||||1970-01-01 00:00:00.000000|773.4|7.89999999999998|53
57945|2022-01-17 18:00:10.077580|2022-01-17 17:00:00.000000||||1970-01-01 00:00:00.000000|774.3|8.79999999999995|53
57999|2022-01-17 19:00:10.076778|2022-01-17 18:00:00.000000||||1970-01-01 00:00:00.000000|775.3|9.79999999999995|53
58053|2022-01-17 20:00:10.082691|2022-01-17 19:00:00.000000||||1970-01-01 00:00:00.000000|776.2|10.7|53
58107|2022-01-17 21:00:10.090738|2022-01-17 20:00:00.000000||||1970-01-01 00:00:00.000000|777.2|11.7|53
58161|2022-01-17 22:00:10.095337|2022-01-17 21:00:00.000000||||1970-01-01 00:00:00.000000|789.57|24.0700000000001|53
58213|2022-01-17 23:00:10.081302|2022-01-17 22:00:00.000000||||1970-01-01 00:00:00.000000|790.28|24.78|53
If you look at this data, you can see the jump at row id 58161 - the data before that is junk.
Be aware: these timestamps are in UTC!
Now, delete the data:
sqlite3> delete from statistics where metadata_id=53 and created <= '2022-01-17 21:00:00';
The results aren’t perfect. The statistics table keeps a running total of the usage in the sum column. So I still have a large jump at the beginning, since the new beginning (row 58161 above) already shows 24.07kWh usage. So now I just need to subtract that previously accumulated usage:
sqlite> update statistics set sum = sum - 24.07 where metadata_id = 53;
If your data includes a reset, sum might behave differently. All of my usage is in January, so the meter hasn’t reset at all. I don’t know how HA behaves in that case yet.
Hope that helps!
Hi, thanks for your help. I found the problematic data and deleted them.
Now the problem is the sum, I need to subtract 23000 only from 58418 until now, not before.
My knowledge in sql is low, I’ll backup the data base and give some trial.
| Éditer | Copier | Supprimer | 58212 | 2022-01-22 11:00:10.186229 | 2 | 2022-01-22 10:00:00.000000 | NULL | NULL | NULL | 2021-09-02 22:00:00.008340 | 0 | 1286.0159861173522 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Éditer | Copier | Supprimer | 58227 | 2022-01-22 12:00:10.193095 | 2 | 2022-01-22 11:00:00.000000 | NULL | NULL | NULL | 2021-09-02 22:00:00.008340 | 0 | 1286.0159861173522 | |
| Éditer | Copier | Supprimer | 58242 | 2022-01-22 13:00:10.136917 | 2 | 2022-01-22 12:00:00.000000 | NULL | NULL | NULL | 2021-09-02 22:00:00.008340 | 0 | 1286.0159861173522 | |
| Éditer | Copier | Supprimer | 58257 | 2022-01-22 14:00:10.153692 | 2 | 2022-01-22 13:00:00.000000 | NULL | NULL | NULL | 2021-09-02 22:00:00.008340 | 846.054 | 2132.069986117352 | |
| Éditer | Copier | Supprimer | 58290 | 2022-01-22 16:00:10.168745 | 2 | 2022-01-22 15:00:00.000000 | NULL | NULL | NULL | 2021-09-02 22:00:00.008340 | 846.472 | 2132.4879861173517 | |
| Éditer | Copier | Supprimer | 58306 | 2022-01-22 17:00:10.124139 | 2 | 2022-01-22 16:00:00.000000 | NULL | NULL | NULL | 2021-09-02 22:00:00.008340 | 846.472 | 2132.4879861173517 | |
| Éditer | Copier | Supprimer | 58322 | 2022-01-22 18:00:10.112710 | 2 | 2022-01-22 17:00:00.000000 | NULL | NULL | NULL | 2021-09-02 22:00:00.008340 | 846.472 | 2132.4879861173517 | |
| Éditer | Copier | Supprimer | 58338 | 2022-01-22 19:00:10.154397 | 2 | 2022-01-22 18:00:00.000000 | NULL | NULL | NULL | 2021-09-02 22:00:00.008340 | 846.472 | 2132.4879861173517 | |
| Éditer | Copier | Supprimer | 58354 | 2022-01-22 20:00:10.803494 | 2 | 2022-01-22 19:00:00.000000 | NULL | NULL | NULL | 2021-09-02 22:00:00.008340 | 846.472 | 2132.4879861173517 | |
| Éditer | Copier | Supprimer | 58418 | 2022-01-23 00:00:10.154214 | 2 | 2022-01-22 23:00:00.000000 | NULL | NULL | NULL | 2021-09-02 22:00:00.008340 | 2 | 25188.52398611735 | |
| Éditer | Copier | Supprimer | 58434 | 2022-01-23 01:00:10.126510 | 2 | 2022-01-23 00:00:00.000000 | NULL | NULL | NULL | 2021-09-02 22:00:00.008340 | 2.129 | 25188.652986117355 | |
| Éditer | Copier | Supprimer | 58450 | 2022-01-23 02:00:10.118385 | 2 | 2022-01-23 01:00:00.000000 | NULL | NULL | NULL | 2021-09-02 22:00:00.008340 | 2.257 | 25188.78098611736 | |
| Éditer | Copier | Supprimer | 58466 | 2022-01-23 03:00:10.130135 | 2 | 2022-01-23 02:00:00.000000 | NULL | NULL | NULL | 2021-09-02 22:00:00.008340 | 2.391 | 25188.914986117365 | |
| Éditer | Copier | Supprimer | 58482 | 2022-01-23 04:00:10.126142 | 2 | 2022-01-23 03:00:00.000000 | NULL | NULL | NULL | 2021-09-02 22:00:00.008340 | 2.514 | 25189.037986117368 | |
You can filter, either on the id field or even by the created field:
update statistics set sum = sum - 24.07 where metadata_id = 53 and created > "2021-01-22 12:34:56"
I just realized that my fix didn’t work quite right. My energy dashboard now has a jump in the sum at the time I ran the updates above. Maybe HA has that value loaded already - I’m going to try again with HA stopped this time.
Ich pretty sure the values in the table statistics are calculated from the table statistics_short_term. So you would have to correct your values there as well.
And you might have to correct the values in table states (at least the current state) as well, as they are the origin for calculating the statistics.
Yea, you’re right - though modifying states looks hopeless since a new reading won’t be continuous with the old ones.
I might undo this by moving all of the excess back to the very first entry. It’ll still be an incorrect jump, but at least I won’t see it.
I tried a fresh start by removing the energy sensors and re-adding, but that didn’t clear out the data. As soon as I re-added, everything from the past was visible again.
Thanks for this.
Had my daily energy rollover and include all of yesterday! I found the metadata_id in phpmyAdmin and this query worked the treat. Using the date just seemed to align with my brain a little better than other queries.