Energy Management in Home Assistant

[e-raser]

@arkoko

You can try serach from states table:
select state_id, state, last_changed, last_updated, old_state_id from "states" where entity_id="sensor.your_sensor_name_energy" order by state_id desc;
Or from statistics, if this graph from energy tab:
select * from "statistics" where metadata_id=(select id from "statistics_meta" where statistic_id="sensor.your_sensor_name_energy") order by id desc;
Or from short_term statistics table:
select * from "statistics_short_term" where metadata_id=(select id from "statistics_meta" where statistic_id="sensor.your_sensor_name_energy") order by id desc;
And if you find abnormal row, you can delete it by id number or update to the normal value, like this:

update "states" set state="1472.85" where state_id=244913;
update "statistics" set sum=4095.6 where id=11505;
update "statistics_short_term" set state=177.93 where id=311434;

Thank you very much, that was a kickstart for my “finally fix the energy dashboard” job!
I could find the “accident hour” with your slightly adjusted select statement (using some loooong time ago SQL basic knowledge ):

SELECT * FROM "statistics"
WHERE metadata_id=(SELECT id from "statistics_meta" WHERE statistic_id="sensor.energy_total_total") AND created LIKE "2021-11-14%"
ORDER BY id ASC
LIMIT 100;

Now I face the following situation which makes the whole operation a bit tricky:
It’s not just one row which needs to be updated, but ALL rows after the “accident hour” up to now (today):

while state column values proceeded normal (sensor values), the sum column values (which is the one used for the energy dashboard statistics) proceeded with the added value. Visually:

id 	    created						metadata_id	start 						mean 	min 	max 	last_reset 					state 	sum
425609 	2021-11-14 16:00:11.111377 	133 		2021-11-14 15:00:00.000000 	NULL 	NULL 	NULL 	1970-01-01 00:00:00.000000 	364.33 	224.10999999999999
425773 	2021-11-14 17:00:11.159665 	133 		2021-11-14 16:00:00.000000 	NULL 	NULL 	NULL 	1970-01-01 00:00:00.000000 	364.49 	224.27
   ===> sum column rows above (before) this line are fine, sum column rows below (after) this line are *WRONG*<===
425937 	2021-11-14 18:00:11.045548 	133 		2021-11-14 17:00:00.000000 	NULL 	NULL 	NULL 	2021-06-04 00:00:00.000000 	364.67 	588.99
426106 	2021-11-14 19:00:11.148599 	133 		2021-11-14 18:00:00.000000 	NULL 	NULL 	NULL 	2021-06-04 00:00:00.000000 	364.86 	589.1799999999998 

Therefore the following questions:

1. MOST IMPORTANT: How to do a mass change of the sum field of all rows (where id greater than 425773 and metadata_id=133) up to today including an on the fly calculation? It needs to use the old value and reduce that by a fixed amount.
   Is there a SQL UPDATE statement for this (calculation included)?
   Example: value before = 500, value after update should be 200. Sth. like “update value minus 300” is what I’m looking for.
   Update 2022-01-15:
   UPDATE statistics SET sum=sum-300 WHERE metadata_id=133 AND id > 425773; according to Energy Management in Home Assistant - #1128 by arkoko seems to have worked without any notable side effects. Energy Dashboard is now showing correct data again (finally). Thanks @arkoko !
   Update 2 hours later:
   There were some side effects. Like no generation of new hourly bars in energy dashboard. Reason: I forgot to also update the sum values of the affected total sensor in the statistics_short_term table. So an additional UPDATE statistics_short_term SET sum=sum-364.54 WHERE metadata_id=133 AND id >= 3703032; did the trick where the id was the oldest one available in the table for that sensor.
   Learnings: both tables need to be updated. Values in statistics_short_term (earliest available row depends on recorder keep_days or purge settings, in my case 14 days so 1st of January was the oldest available row) is generated every 5 minutes, statistics only hourly (which should be called “statistics_long_term” as it records all values from the beginning and does not purge it. Only this way e. g. the Energy Dashboard can show graphs of numbers older than the recorder keep_days / purge setting.

2. Will the rest of the energy dashboard (week, month and year stats for example) “fix” itself after the update? Or do I need to fix other parts as well?
   I believe first is true (will fix itself) based on few spot checks aka “reverse engineering” :
   a) year: the energy total value in the year 2021 view is simply the last value of column sum with created = 2021-12-31 23:00:12.986483.
   
   
   b) month: the energy total value in the month November 2021 view is simply calculated by "last value of last day of current month (e. g. November)" minus "last value of last day of previous month (e. g. October)". I checked this with December and it matches perfectly.
   Update 2022-01-15:
   Currently (after performing the database changes) no negative side effects (or any at all) discovered.

3. What about the costs? My energy total sensor is configured to use an entity which keeps track of the total costs.
   → based on the database entries I’m very sure I configured this after the “accident” according to first row for the corresponding metadata_id of my total cost sensor. So at least that’s fine, nothing I need to care about.

4. Would I need to fix my InfluxDB data as well?
   Currently I pump everything into the InfluxDB. As of today I can’t imagine why I even would like to use that energy dashboard visualization data from InfluxDB.
   And: based on a quick check those data isn’t even stored, InfluxDB “only” catches all sensor data. That energy dashboard data isn’t from states table (objects like “total power consumption entity” for example) but from statistics table - that data is not copied to InfluxDB.

So with a proper command for question #1 I should be almost there… SQL experts like @arkoko welcome!