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

[malosaa (Hector)]

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.

[vingerha]

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

[vingerha]

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

[malosaa (Hector)]

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…

[vingerha]

Possibly systen time compared to last consumption…too many options

@malosaa

why does it create 2 wrong sums

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

[malosaa (Hector)]

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.

[vingerha]

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

[malosaa (Hector)]

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

Migrating home assistant database from sqlite to mariadb.. ONLY if you are VERY familiar with Database administration

First I deleted the entities (see below) [image] Then I deleted all data (see below) [image] But you are right: it didn’t solve anything. Will now perform steps again and then go for a fixed value (as you indicated in the below post)

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

[malosaa (Hector)]

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

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 ?

[vingerha]

Just an FYI, I updated my water/gas manually today and had a restart shortly after…costs are completely wrong now
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

[malosaa (Hector)]

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

[vingerha]

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)?

[malosaa (Hector)]

@vingerha

Can you possibly migrate the old costs to this new entity (sql insert)?

i love to try, can you tell me how i can do this ?
but the thing is the old device is still getting updated…

My new device ID is 135
my old device ID is 29

[malosaa (Hector)]

Still some wrong calculation…

this is my cost entity

This is how it calculates with a new entity…

this is how i set it up

Why is it wrong again, with a total new entity ?

[vingerha]

I have this
The first one is input helper with manualle entered data
The second one is generated by HA, i.e. in my energy dashboard settings for gas_usage I use a fixed price so the one you see shows ‘today’ costs which are 0 as I did not enter anything today

From what i can see you seem to use a separate entity for the price not? Maybe that could be an issue??? But on the other hand it worked in SQLite

[malosaa (Hector)]

yes, its really strange, look at this:

This is all based on

WHen i select this entity as a total cost tracker it shows up this:

      - name: "Daily Energy Cost3"
        device_class: monetary
        unit_of_measurement: EUR
        state_class: total
        state: >
           {{ (states('sensor.p1_gas_consumed_cumulative')|float(0))}}

Also wrong calculation because the price is 0.85.

WHen i select this, this is my manual gasprice in a input device :

      - name: "Daily Energy Cost2"
        device_class: gas
        unit_of_measurement: m³
        state_class: total_increasing
        state: >
           {{ (states('input_number.gasprice')|float(0))}}

i get this

If i select this entity:

      - name: "GasTestCostCalculatorTotal"
        device_class: monetary
        unit_of_measurement: EUR
        state_class: total_increasing
        state: >
           {{ (states('sensor.gas_helper_test')|float()) + (states('sensor.today_gas_total_cost')|float())}}

i get this

Now when choosing:

template:
  - sensor:
      - name: Today GAS Total Cost
        device_class: monetary
        state_class: measurement
        unit_of_measurement: EUR/m³
        state: >
          {{ (states('input_number.gasprice')|float(0))}}

When selecting this helper as an cost device

also nothing

[vingerha]

If it weren’t so sad … I would be LOL, I hope someone picks up on your github ticket
I will enter new values today…fingers crossed

[malosaa (Hector)]

haha true, this is so annoying, getting crazy of it.

But i found out something else.
I did create another entity that collecs the data of my gas-usage.
So when i selected that entity, and selected the my entity with colletcing the total cost it works.

There is a stupid thing happening, check this out.

i made this entity some hours ago:

     - 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

but it did somehow autocreate this entity, i did not make that one

Could that be the problem whats going on?? HA create a random new device based on the selected entity in the dashboard…

[vingerha]

Story continues, my zigbee device transmitting from the elec meter had a connection issue for more than 1 day. Repaired yesterday…history of costs (!) all gone, I did not change the device and my consumption is still OK…yippie

[vingerha]

So here is what I did …awaiting the next issue
0. made sure elect costs are no longer calculated (setting in Energy dashboard)

1. I removed all 5 cost sensors from /config/.storage/core_entity_registry
2. I removed all cost-sensor stuff from statistics and statistics_meta and statistics_short_term
3. I removed all states for these (was not too easy)
4. I reinstalled the cost sensor via energy dashboard, sensor.linky_base_cost now showing as only active one
5. I recreated the costs using the consumption sensor stats, inserting in statistics using sum = sum * 0.175, 0.175 my cost per kWh
   The dashboard now shows fine again, I need to wait for the next 2 hrs if this stays fine

EDIT: why I needed to do this was because data from the original sensor.linky_base_cost ended up in sensor.linky_base_cost_2 and bits and pieces in _3 … _5…a total mess