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…
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.
