`Energy` dashboard showing huge, negative values

[AleXSR700]

Checklist

• [X] I have updated to the latest available Home Assistant version.
• [X] I have cleared the cache of my browser.
• [X] I have tried a different browser to see if it is related to my browser.
• [ ] I have tried reproducing the issue in safe mode to rule out problems with unsupported custom resources.

Describe the issue you are experiencing

Hello everyone, after cleaning my database (purging all statistics_short_term, vacuuming etc.) I am seeing negative values for all the energy today sensors in my Energy dashboard for the day before restarting Home Assistant (so the day of the cleaning).

However, there are no negative values in the database.

Execution finished without errors.
Result: 0 rows returned in 17182ms
At line 1:
SELECT *
FROM states
WHERE state < 0
  AND metadata_id IN (
    SELECT metadata_id
    FROM states_meta
    WHERE entity_id LIKE '%energy_today%'
  );

From what I gather, the Energy dashboard should be using LTS, which makes sense since it only needs hourly values. So deleting the STS data should have no impact on the database. Even if the STS data was being access and missing, there should simply be a gap in data and hence "0" value, should it not?

If the problem is that LTS is now missing, then this should also result in a data gap and hence "0" values for all those days.

tom_l mentioned in Discord that HA would perform the calculation for the last available data point. But that should then also result in a positive value.

So I cannot quite understand why any missing data would cause huge negative values.

Thank you for your support despite this being an unusual case :)

Alex

Describe the behavior you expected

Gap or rather "0" for all dates/times where data is missing.

Steps to reproduce the issue

Difficult to say for sure, but I did

1. Disable auto_purging in recorderintegration
2. SQLite query DELETE FROM statistics_short_term; without manual purging

3. ...

What version of Home Assistant Core has the issue?

2024.8.2

What was the last working version of Home Assistant Core?

No response

In which browser are you experiencing the issue with?

No response

Which operating system are you using to run this browser?

No response

State of relevant entities

No response

Problem-relevant frontend configuration

No response

Javascript errors shown in your browser console/inspector

No response

Additional information

Huge negative values for sensors that should only show smaller positive values:

Normal/all days before that day:

Nothing suspicious in the history either (for a few exemplary entities that according to Energy are e.g. -260 kWh):

[karwosts]

If you want to pursue this lets pick one entity, and please dump and upload the relevant records from the long term and short term statistics table for that entity. From the time before the anomaly, to the next available records afterward.

Given you're doing unsupported things in your database, the ultimate solution here will almost certainly be for you to just use the adjust sum dialog to manually these broken entities. But I'm willing to explore a bit here as an academic exercise.

[AleXSR700]

Sounds good :)

I am away for the weekend and have started some trsting already.

So, what I did which was unsupported:

1. ran a self-written script to clean my states table of redundant data. I will call that db version "cleaned".
2. I purged the entire STS table. I will call that one "cleaned&purged".

Of course I performed a vacuum operation after each step.

I then copied the "cleaned&purged" db to my HA config and started back up.

The result is in the first post.

So there were no STS entries at all. So nothing to check there.

Yesterday (before your post) I then decided to start investigating so I copied the "cleaned" version to HA and started up. No issues in the Energy dashboard. So the cleaning of redundant information had no influence on anything.

So it must be related to step 2, the purging of STS.

To check if the issue is available states data with lack of STS & LTS data, I called the Recorder: Purge action. I then looked at the db an hour later and the STS was still populated. So I am not sure how the recorder works yet (only brought my mobile with me and that is a pain to check code with). So not sure if the STS to LTS downsampling was performed when I called the purge action or not. Or if the manual calling even triggers the purging at all.

So now I ran the manual purge and also let it run while I am away. So it should run that purging every morning again (keep days set to 0 and auto purge to true).

I then now checked my HA system for db size and it has dropped from 4 GB to 200 MB over night. So looks like the auto purging worked and the manual action did not.

When I get home, I will import the LTS data of the now fully purged db to my initial "cleaned&purged" db. That way I should have all LTS entries to go with the states data. Only STS should then be missing.

@karwosts: Which data does the dashboard really need? Could it live off of states only? Or does it require STS or LTS data?

If there are states for a period of time but no STS or LTS data, what will it/should it show?

I only have the one db of course, so I cannot check if purging STS always causes that behavior. If it does, then I would think there is a logic error in the scripts. And if not, then it seems something is strange about my specific case :-)

[AleXSR700]

EDIT: In my initial post I thought that there was data missing. But in really metadata_id is not a unique identifier. It is different for statistics and states tables. So I updated my post now.

@karwosts Update: Having returned home I imported statistics and statistics_runs to my cleaned (and working) db. I then deleted the content of statistics_short_term. Same result as before, the Energy dashboard shows strange values.

In this chart you can see the time from 23.08. - 01.09..

Now here is the same range except excluding today, where Energy performed its calculations:

So I looked at my AV preamp

Here the Energy plot for today with -259.38 kWh, which obviously makes no sense as a value :)

And here the data from states and statistics. FYI, they are from the db as copied to HA, not from the db of HA after restarting. If Energywrites anything to the db, I can download and check that for you too.

av_states.csv av_statistics.csv

Please let me know what I can check for you and what I can supply next :)

Thank you for your support!

Alex

[AleXSR700]

After letting it run over night, here also the export of states and statistics for the AV preamp entity of the "booted" database.

So same as above but after booting the database up and letting it run over night.

av_states_booted.csv av_statistics_booted.csv

Where does Energy dashboard store its calculated data?

I think I may I have an idea why this might be happening, but then there would be some kind of logic error in the code: I have all entities as Return to grid and only my Shelly 3EM as Grid consumption. That allows me to check total consumption versus tracked consumption.

Idea

Could it be that the Energy dashboard is looking for data with a negative sign in the database because they are listed as Return to grid and then performs a wrong calculation for the gap period? Do you know what I mean? It thinks the data should be negative but the data is in fact positive. So there will be multiple positive values for energy_today and Energy expects negative values. So instead of working with the abs() it calculates with the actual signs and then returns very large, false values?

Maybe Energy correctly changes the sign internally when you add a "positive" entity to Return to grid but then does not calculate it correctly when there is a database gap?

[karwosts]

Where does Energy dashboard store its calculated data?

It doesn't store anything. When it renders it requests data from the recorder/statistics_during_period API. All the data is stored in the recorder in the statistics tables, not specifically for energy.

I can see in your last upload that recorder is resetting the sum of this entity, I believe that would cause a -98.9 value to be reported in this hour for energy. The sum is not supposed to reset to 0 if everything is working properly.

[AleXSR700]

Interesting! If I see it correctly, those rows have mean, min and max NULL. So erroneous rows would match

SELECT *
FROM statistics
WHERE 
    mean IS NULL 
    AND min IS NULL 
    AND max IS NULL
    AND state <> 0
    AND sum = 0;

?

I now created a log from the first boot after copying the database to HA.

home-assistant.log

It should only contain recorder and, if even a thing, energy entries (everything else is set to critical.

I could have sworn that the Energy dashboard briefly showed proper values during boot/immediately after. And then the strange values appeared.

Is any of the calculations being performed by Energy or are they all being performed by the recorder integration?