A few bad stats for total_increasing

[kurtzettel]

I have a few entities that are really struggling with stats. I’ve tried to correct the underlying data in the mariadb database but each hour it generates a new stat that is way off the mark. I have 3 examples but the one I’ll focus on is my space heater total kwh:

It shows as using 44,744 kWh. This is a filter entity:

  - platform: filter
    name: "Office Space Heater [kWh]"
    entity_id: sensor.office_space_heater_raw_kwh
    filters:
      - filter: outlier
        window_size: 10
        radius: 5.0

The underlying sensor, office_space_heater_raw_kwh likes to randomly provide a super high value but in this case, I don’t see any of those in the state table:
SELECT * FROM states where entity_id = "sensor.office_space_heater_raw_kwh" and state > 2;
Returns 0 rows. The same with
SELECT * FROM states where entity_id = "sensor.office_space_heater_kwh" and state > 2;
The sensor itself has a pretty normal value:

If I run the query:
SELECT s.* FROM statistics s, statistics_meta sm where s.metadata_id = sm.id and sm.statistic_id = "sensor.office_space_heater_kwh" order by start DESC;
I see the problem statistics:

If I update the two most recent so sum = state the graph is fixed for now but it’ll break again the next hour.

I’m seeing similar challenges with 2 other entities, both are total_increasing.

My questions are:

1. How is sum calculated for a statistic?
2. For a total_increasing entity, how does it determine the base value/last reset value?
3. Any other recommendations for fixing this?

[ondras12345 (Ondra)]

Insert Historical Power and Gas Data into database

From looking at the source code, it seems like there are 5-minute statistics that you’d need to modify as well as long-term (hourly) statistics:

There is also a new tool for statistics that should allow you to correct errors without directly modifying the database.

[kurtzettel]

The tool didn’t work for me but I wasn’t updating the short-term stats when I was messing around in the database. I’ll try that!
I’ve also added some availability_template values to my entities so they don’t zero out every restart:

      gas_total:
        friendly_name: "Gas Total"
        device_class: gas
        unit_of_measurement: ft³
        value_template: "{{ states('sensor.ert_scm_45647446_consumption') }}"
        availability_template: "{{ states('sensor.ert_scm_45647446_consumption') | float(0) > 0.1 }}"
      gas_cost:
        friendly_name: 'Gas Cost' 
        value_template: "{{ (states('sensor.png_gas_price_per_ft3')|float * (states('sensor.gas_total')|float - 411780))| round(2) }}"
        unit_of_measurement: 'USD'
        availability_template: "{{ states('sensor.gas_total') | float(0) > 0.1 and states('sensor.png_gas_price_per_ft3')|float(0)  > 0.00001 }}"