2022.4 - Energy Dashboard Recalculate After Adjusting Long-Term Statistics - (WAS Best Way To Edit Energy Dashboard Data? Remove or Edit Bogus Data on One Day from Tesla Powerwall)

[ziptbm]

What’s the best way to remove or edit data from the energy dashboard?

I set up my dashboard with active sensors feeding it data about 2 months ago and for some unknown reason, I got a bunch of bogus data on February 23rd (e.g. my home used 2,045 kWh of power and my solar generated 486 kWh).

It’s messing up any of the aggregate views and historical data, so I’d ideally like to edit the records to something more accurate, but would settle for simply deleting it out. I would assume this would involve some queries to the database (using MariaDB), but I am a completely n00b on SQL, so want to make sure I’m heading in the right direction and get any pointers before messing up my system. Any pointers or suggestions?

[tom_l]

How to fix statistics data (e.g. energy data)

Hey I have managed (I think) to solve the issue which was bothering me for a long time and I have decided to share the solution with you guys. I hope that someone finds it helpful. Problem I have OWL energy sensor which sends the data via RF433. The problem is that sometimes the received values are not correct. Pretty often I get “0” as the energy_total and once the value jumped significantly by 78M. [image] I don’t have the sensor data to investigate what has happened that day and what wer…

[tom_l]

An even easier way will be available in 2022.4

[ziptbm]

That’s great to hear!

I’m actually using MariaDB, but am trying to figure out how to fix this from the guide that is using MySQL. I’m not familiar with either db tech, so I’m trying to learn quickly…haven’t gotten far yet.

[ziptbm]

2022.4 made it super simple to edit the rouge data points for the sensors, so it’s reflected correctly in the source data.

However, the Energy Consumed data at the top of the Energy Dashboard still reflects the ridiculous spiked data. Does anyone know how to have it recalculate that part? I made the changes yesterday, so it’s been more than 2 hours since that happened, but there have been no changes.

[SpikeyGG (Greg G.)]

This method works perfectly for bogus statistics data points but what about raw sensor data that is bogus? I have some of these I’d like to correct but it doesn’t look like there’s an easy way to correct those using the HA GUI.

[FajnB]

I know this is over a year and a month old by now, but if you use MariaDB you can edit the raw data in statistics by installing phpMyAdmin add-on.

!!!
THIS IS VERY RISKY IF YOU DO NOT KNOW WHAT YOU ARE DOING AS YOU WILL BE EDITING LIVE DATABASE THUS THERE IS A RISK OF CORRUPTING THE DATABASE IT SELF AND POTENTIALLY RENDERING YOUR HOME ASSISTANT INSTALLATION NON FUNCTIONAL
!!!

If you still choose to proceed make a FULL backup of home assistant so if it corrupts you can go back.
_{You have been warned. I am not responsible for any data loss / home assistant installation corruption.}

There you’ll go to “statistics_meta” table and search for your desired sensor. After you find it make note of the “id”. Then go to the “statistics” table and use command like this: SELECT * FROM `statistics` WHERE metadata_id = 12 AND start_ts > 1711234800 AND start_ts < 1711324800 ORDER BY `id` DESC LIMIT 100;
This command will search in table “statistics” for any data containing “metadata_id” 12 (in your case the “id” from “statistics_meta” table I told you to make note of). Then it only tries to find data that has been saved between 1711234800 and 1711324800. These are timestamps in so called epoch (it is time in seconds starting from midnight 1.1.1970). In this example I am looking for data between 23.3.2024 23:00 - 25.3.2024 00:00. You can convert time to epoch and epoch to time here: https://www.epochconverter.com/
After the data is loaded, you can the double click on the wrong data in the “state” column and edit it.
NOTE: Remember that decimal point have to be a dot, not a comma !