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

malosaa · November 30, 2022, 1:03pm

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 · November 30, 2022, 1:12pm

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 · November 30, 2022, 1:17pm

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 · December 1, 2022, 5:45am

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 · December 1, 2022, 9:22am

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

I removed all 5 cost sensors from /config/.storage/core_entity_registry
I removed all cost-sensor stuff from statistics and statistics_meta and statistics_short_term
I removed all states for these (was not too easy)
I reinstalled the cost sensor via energy dashboard, sensor.linky_base_cost now showing as only active one
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

malosaa · December 1, 2022, 10:42am

Thanks for your info.
I think i got it working, i will also wait again from now on 30 minutes, as my new device what i created had + instead of * so if that works, i need to try how i can import the old data into this new device. but lets wait.

I also will report at 12:00 and see if it did calculate, but i think its correct.
I’m using a total cost device, using this template

So this (device) entity is called sensor.calculategaspricetest, this calculated the value i use in my helper

this is the template from the helper.

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

this is the helper

this is the total entity calculated with the template from above and the helper

      - 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())}}

SO the above one creates the total EUR what i use in the energy dashboard, and like now it looks ok.
Fingers crossed.

vingerha · December 1, 2022, 10:50am

I used:

insert into statistics (created,start,state,sum,metadata_id) select created, start, 0, sum*0.175, [costsensor] where metadata_id = [consumptionsensor]

malosaa · December 1, 2022, 11:04am

Yes it works, calculation is correct now, using a TOTAL entity with the cost.

Thanks for the code, but i want to import all old costs, what where recorded in my database into this new one because my old costs are changed daily, but i want to import till yesterday and not today.

How can i do that ?
My new device ID is 135
my old device ID is 29

vingerha · December 1, 2022, 11:26am

add a where-clause to exclude today

insert into statistics (created,start,state,sum,metadata_id) select created, start, 0, sum, [NEWcostsensor] from statistics where metadata_id = [OLDcostsensor] and start < '2022-12-01'

Note that if the newcostsensor already has values before today then it will throw an error as duplicates are not allowed

vingerha · December 1, 2022, 11:28am

In the meantime…costs screwed up again, since I did not add anything to short_term, this started from scratch and now overwrites the statistics value…

malosaa · December 1, 2022, 11:38am

Try it like i did, it works.
ANd thanks for the code, i will try it when i do test a new migration and see if it gets corrected.

But i did find out HASS calculation does not work as expected.
I show u why:

This is my utility meter helper:

It reads the data from this entity:

See the difference…
The source is reporting: 0.68425
and the utility meter is reporting: 0.68935

vingerha · December 1, 2022, 11:41am

Mine works too but I have just screwed up too much with removing data. I mended the last short_term record to show the correct ‘total’ and now it shows fine again…not 100% confident after last nights reset of course … in other words, I know how to construct the right values now but I am totally in the dark as to when a reset takes place and why for pete’s sake it created 4 cost sensors since yesterday wihtout taking the correct values.

vingerha · December 1, 2022, 11:42am

This is what I changed in short_term and now it picks up corrcetly

malosaa · December 1, 2022, 11:43am

Yes its weird, it just auto creates costs sensors, if u delete one of them the data is gone forever…

malosaa · December 1, 2022, 11:43am

So you think the problem is in the short_term ?

vingerha · December 1, 2022, 11:45am

That is my current guesswork, possibly the reset screws up short_term and then HA thinks…oh…let’s create a new one…

malosaa · December 1, 2022, 11:47am

Well lets try it, i will do the migration again, it takes arround 40 minutes and
i will try to correct the short_term and see if it get fixed.

If not, then ill do start with adding the new cost TOTAL entity what i created yesterday, and see if it calculates right.
If that does, i will use your code to import the old data into the today created one.

Because a total entity should behave like a total, and no calculation is done in the EnergyDB… so it should work normal, but what i dont know is if i can import the data from the (entity containing a fixed price) to a total entity… thats the question.

vingerha · December 1, 2022, 12:01pm

I opened a case with HA now but it is too hard for me to create a replication of it, asking for insight in how this works which may then explain the mismatches…still, this seems very much a bug but we need to know the cause to tackle it

malosaa · December 1, 2022, 12:05pm

Well, with my issue i can replicate the issue, by just using the migration script.
This only causes that the cost sensor getting messed up, while the rest works perfect with no issue…

vingerha · December 1, 2022, 12:08pm

Which is also the only issue with me,…more guesswork is a timeing mismatch between 'now, short_term and stats which then results in a extra reset…
Your issue is even harder to grasp as you did a full import, your old HA continues fine so why oh why.