Energy Dashboard miscalculating energy cost

Hi to everyone,
I’m experiencing a strange behaviour after converting the DB from sqlite to mysql:
I have a sonoff POW3 connected between the grid and my home electrical system with this configuration:

#Contabilizzatore energia
    source: sensor.contatore_principale_energy_today
    name: Contatore Mensile
    cycle: monthly
      - F1
      - F2
      - F3

until I used the sqlite DB everything went fine, but when I moved to mariadb the sum went nuts

other than that I have not changed anything. I also changed the €/KW/h cost to only 2 decimal digits (0,14 instead of 0,1436) but nothing changed.

what else should I check?

I deleted the cost, rebooted, re-added the cost, rebooted. Same behaviour.
Now I’ve tried to add a daily-based meter

    source: sensor.contatore_principale_energy_today
    name: Contatore Giornaliero
    cycle: daily
      - F1
      - F2
      - F3

on the same sensor.
The result is the same:

0,65 kWh → 1,12 € with a fixed price of 0,143 EUR/KWH

maybe a small bump?

I had the same happen when I moved to MariaDB the other day. The cost calculation with fixed price went nuts, quickly increasing to ludicrous values.

I tracked down what seems to be happening.


In the database, the “statistics_meta” table, there’s an entry for statistics for the entity tracking cost
(14 just happens to be the id that my cost entity has in this table):

SELECT * FROM statistics_meta where id = 14;

Selecting the last rows of table “statistics_short_term” for this id:

SELECT * FROM statistics_short_term where metadata_id = 14 and last_reset >= '2022-09-12 17:55:41';

it is apparent that the sum is not calculated correctly. The sum for each row (with last_reset identical to the previous row) should be:

sum = previous_row_sum + (state - previous_row_state)

but it is actually:

sum = previous_row_sum + state

The same goes for the table “statistics” with metadata_id = 14 (the id for my cost statistics meta).

The actual consumed energy statistics still behave well, though. It’s just the cost that is miscalculated.

HA entity storage

In the file config/.storage/core.entity_registry theres an automatically created entity for the cost:

        "area_id": null,
        "capabilities": {
          "state_class": "total"
        "config_entry_id": null,
        "device_class": null,
        "device_id": null,
        "disabled_by": null,
        "entity_category": null,
        "entity_id": "sensor.frient_elmatare_energy_cost",
        "hidden_by": "integration",
        "icon": null,
        "id": "aad8d1905fe12add7a2733ba608497fb",
        "has_entity_name": false,
        "name": null,
        "options": {},
        "original_device_class": "monetary",
        "original_icon": null,
        "original_name": null,
        "platform": "energy",
        "supported_features": 0,
        "unique_id": "06a71af988b922338f15f27e36551733_grid_cost",
        "unit_of_measurement": "SEK"

which i think should have “state_class”: “total_increasing” instead of “total”. This cannot be changed, since HA overwrites this file on startup.

I ran a modified MySql Procedure originally posted here to recalculate the sums, but it only fixes the past, the future continues to get screwed up.

For some reason, HA now seems to do the statistics calculation wrong.

There is still no resolution to this behavior.