Bulk statistics adjustment

I see that it is now possible, via:

Dev > Statistics

To adjust individual erroneous stats.

I have some sensors that have been running for some years, and sporadically given a stats value that is a real outlier - so for instance 50000 when a normal value would be 5

Other than manually finding and correcting each one, is there a method to do a bulk fix for a sensor over all time - along the lines of “Set value = 0 where value > 50000” ?

If you a running MySQL, you can do that through a SQL update command

I’m running hassio with an sqlite database - including the web viewer for sqlite.
Doing some digging through the tables, I have a bit of an idea what is going on…

I have two sensors:

sensor.home_electricity_meter - metadata_id 227
and
sensor.home_gas_meter -metadata_id 232

The real gas reading is ~60000
The real electricity reading is ~14000

However, at apparently random times during the day, the electricity meter history tab within Home assistant shows a single gas reading, and vice versa - causing the stats problems

An example is given below - showing the real electricity values during a day, followed by a value of almost 60000. The same thing occurs in reverse as well.

Checking the database for that specific value, it only appears in the stats tables against the gas ID:

as it should - so not sure how it is getting into the electricity stats, as can be seen in the plot

(the same occurs in reverse from electricity to gas - at equally apparently random times of day

The sensors are defined in configurtation.yaml as:

  - platform: mqtt
    name: "Home Electricity Meter"
    state_topic: "SMART/LAB/5410ECA1BC1F"
    unit_of_measurement: 'kWh'
    device_class: energy
    state_class: total_increasing
    value_template: >
      {% if (value_json['elecMtr']['0702']['00']['00']|int(base=16) * value_json['elecMtr']['0702']['03']['01']|int(base=16) / value_json['elecMtr']['0702']['03']['02']|int(base=16))|int > 50000 %}
          {{ states('sensor.home_electricity_meter') }}
      {% else %}
          {{ value_json['elecMtr']['0702']['00']['00']|int(base=16) * value_json['elecMtr']['0702']['03']['01']|int(base=16) / value_json['elecMtr']['0702']['03']['02']|int(base=16) }}
      {% endif %}

 
    icon: 'mdi:counter'

  - platform: mqtt
    name: "Home Gas Meter"
    state_topic: "SMART/LAB/5410ECA1BC1F"
    unit_of_measurement: 'kWh'
    device_class: energy
    state_class: total_increasing
    icon: 'mdi:counter'
    value_template: >
      {% if (value_json['gasMtr']['0702']['00']['00']|int(base=16) * value_json['gasMtr']['0702']['03']['01']|int(base=16) / value_json['gasMtr']['0702']['03']['02']|int(base=16))|int < 50000 %}
        {{ states('sensor.home_gas_meter') }}
      {% else %}
        {{ value_json['gasMtr']['0702']['00']['00']|int(base=16) * value_json['gasMtr']['0702']['03']['01']|int(base=16) / value_json['gasMtr']['0702']['03']['02']|int(base=16) }}
      {% endif %}

I would suspect the problem lay with the source data - except were that the case, then I should see the “wrong” data when I query the database - which apparently I do not - I only see it in the plots…

1 Like

That is strange for sure.

What does the input sensor data look like around that time window? I don’t think that was included in your screen shots.

Hi
It seems that there was a coresponding drop in the gas figures (see below) - so the data swapped at the same time. But it seems odd that in the CSV export I couldn’t see this swap…