Help needed! Energy database costs calculation wrong, after investigating everything

My stats short term was screwed up and no clue where it got its figures from
But it seems that this populates statistics main table as I first repaired main table values, then took a bit of time to repair short-term…in the meantime main table again got the wrong value… it is a mystery to me what/how this is working but the repair sucks.
At somewhat the same time (1 min apart) I registered my water meter, also manual, same sensor setup and all… no problems there, really odd

Yeah but it ‘repairs’ itself too, I showed you my calc which I ran on the whole of your table, only the last 3 rows preseted odd data, guesswork this too from short_term?
EDIT: possibly the reset is when it cleans the stats_short_term, it copies some stuff over to stats … I need to have more time for this too…out now for the rest of the afternoon…sigh :frowning:

Maybe, you know what i can do, drop the tble again and post the data here and see whats going on .

Ok what i did do now,

pressed delete on them, so they dissapear from everywhere

this how it looks now, no data at all in the DB

  • also i found out that data is also in the states table, so i deleted all the way back where it went wrong.

just no worries, ill update you, and you let me know when you have more time. no rush tho.

lets wait now. ill update this post when i have more data after the first update.

//UPDATE 1

DId not work, see below:
this is from gas device

Offpeak power

Produced offpeak

dashboard now after 1 update

//UPDATE 2

i created an entity with a custom price, setup with 0.85.
I selected use an entity with the current price, and i deleted all gas data from the previous one.
so lets test again, but this time only gas data.

//UPDATE 3

Found the problem, its the fixed price configuration.
Using a entity with the current cost solve the issue.
What i did was create a input helper (unit_of_measurement: EUR/m³) and it started using the correct value

Did you delete from statistics_short_term too? This contains the ‘recent’ running total of usage/cost and it copies over so I seem to see.
But…aside trying fixing things after migration… this exercise is just bringing some insight, the migration should work imho. From what you sent, the data looked identical so I see no reason why it would not continue as if HA was briefly shutdown. Since it is only the costs (or?), I suspect a bug …also since the hickup with me 2d ago.
My solution to that was:
statistics: make sure the summation sticks (see way above), I would delete all of the new records, just add one for one day and accept a short-term low level of detail.
statistics_short_term: newest record should have the same value as the newest in statistics (manual update)
… and donot do this short;y before the new stats are calculated or you can start over :slight_smile:

yes yesterday i did test a lot of things.
i did also create a new entity device so it could read the data from there(starting from scratch) it does not work

it still create random data, even when using other entity

see here

t thought i fixed the issue, as the first updatewent good, after the second yesterday it messed up, so i waited till now and it create random data, look at gas its 0 and still has a value, even when using new entty

see my topics here,

So this is what i already did try.

  1. do the migration and after that start using a new entity to read data from current cost, that failed as it still mess up the data.

  2. i did just drop all the cost tables and started over, and that worked (but i don’t want that)

  3. i did just clear the old cost data from the day of the conversion, and start using from that time a new entity that contains the cost data, that worked after 1 update, after the second it messed up again…

So conclusion, the cost on the energy dashboard only work if i really drop all old tables aswell, somehow it does mess up with the old data even when using a new entity, or even deleting all old data before the conversion…

The strange thing is when i press a day back in the energy dashboard it shows correctly, so i though i delete only the data for that day, but that did not work either… so even using new entity or starting from scratch doesn’t work. no matter what.

OK, well… so far the only issue I have see with ‘today’ is statistics_short_term, if there is a gap…shit happens. Again, I still donot understand why a migrated set of data just cannot conitnue, this is no rocket-science database and I tried to find if HA stores data elsewhere but could not find anything in config

True, what i did see after the migration it creates a 0 data in the tables, so then i delete that one and the row before it the wrong data is gone, but after that it starts recording wrong. see here .

this is directly after the first update just after the migration
see the first 2 entries, the second one miss a digit as well (340.91), when deleting the 2 rows it shows up normal.

If i delete the one with the 0 only the € at gas is decreased a lot… so only that 0 creates a random number… and the sum is not correct at all.

image

Yep… the first 2 rows are indeed incorrect but … where do they come from?
With my issue, I had a similar action, I guess (???) that it sees a gap in time and then starts using the consumption to create new entries but does it wrongly…plain guesswork

So I corrected the first two by copying (in this case) 335.728 to these two records
335.728 being the correct value that is

Yes i think so, but why a gap.
As i did during the conversion use a bit of gas so it can calculate. \my migration finished at 18:50 so at 19:00 it did update with the new data and created that 0 value and that value with a missing digit.

Even deleting that one with the 0 value does decrease the price by 200 or something… so the 0 contains a random number.
and when deleting that 340.91 sum it shows the correct data…

So there is an bug with home assistant, there is no other explanation.
but why does it create 2 wrong sums ?

but even if u manual correct the value it starts messing up afterwards…

Possibly systen time compared to last consumption…too many options

No clue, I myself see the same in my db but it corrects itself…beats me how/why

If only one of the devs could reply, it maybe little thing.
Its so frustrating…

Tell me how you did do it with your devices step by step, maybe i’m missing something here.

Well, I can of course not decide for any dev but I would see any migration as non-standard so probably no support. As soon as my ‘bug’ shows up again, I will raise a ticket in github but not sure if/when this will get attention. And unless you are really REALLY interested…going through the core packages is not going to be fun too :slight_smile:

Yeah thats true, well i did open a ticket on github, but as normal nobody reply.
As this issue is not happening to me but also to an other person

he has exactly the same thing after converting, while all other people who did do it to have no problem with the cost… but the difference is that all other people perhaps don’t use EURO as currency

I just stop trying now.
I did delete all the tables from short_statistics and statistics and states form all 4 currency devices that i don’t use anymore
and also from the new ones… so fresh data, and look what happens with the first update

my gas setup
image

and here the entity

WHat i did conclude is that if i go into the DB tables and remove the old data it disappears from the energy DB, so why is it still using the old static costs ?

Just an FYI, I updated my water/gas manually today and had a restart shortly after…costs are completely wrong now :frowning:
Solution: correction of records AGAIN

EDIT: what it seems to have done is register the initial entry as cost but not as usage, then after the reboot I had to re-enter as the consumption did not show up … adding the costs a second time
For me…this is a bug, but no clue where to start as this is also clearly not regular use

Weird!

What i did now worked.
I found out what is causing the problem.

My old entity containing all old data called “sensor.p1_gas_consumed_cumulative”
So i did create a new entity

“sensor.p1_gas_consumed_cumulative” this entity is my live gas consume

      - name: "Gas Usage Graph Total"
        state: >
           {{ (states('sensor.p1_gas_consumed_cumulative')|float(0))}}
        unit_of_measurement: m³
        device_class: gas
        state_class: total_increasing

When importing the data from now on works fine with my custom made entity to track the cost.


It work coreect.

SO conclusion, the old data doesn’t work with the cost sensor when u migrate… creating a new one solves the problem, but i lose the old data.
When i want to see the old data i need to manually select the old entity in the energy dashboard again

If you start anew, I would expect that (but was surprised before)
Can you possibly migrate the old costs to this new entity (sql insert)?