I have a water meter reader which updates over MQTT every 2 minutes. On each update it sends out the meter reading and also the rate (gal/h) since the last reading (2 minutes prior). However, my household often goes several hours without using a drop of water (especially at night). So even though the MQTT sensor gets an update every 2 minutes, when it’s the same value the state doesn’t change and therefore during that period there are no new values stored in the HA recorder database.
That would normally be fine, but the problem is I’m trying to set up a sensor which tells me the average water consumption over the past 2 hours. I’m using the statistics sensor with the MQTT rate sensor as the source sensor, and I’m using average_step. The problem occurs in 3 scenarios:
- Long period without updates/changes prior and into the beginning of the 2-hour window: In this scenario, the entire 2-hour window isn’t taken into account. So if there was a sensor update 2.5 hours ago, and then nothing until a few updates within the past 15 minutes, the statistics sensor will report only the average usage over the past 15 minutes. It completely ignores the fact that there was no water consumption for 1.75 hours. This will cause the sensor to report much larger values than reality.
- Long period without updates/changes at the end of the 2-hour window: In this scenario, there has been no water consumption for a long time (but less than 2 hours). The statistics sensor doesn’t have a new value to use, so the average should be trending down, but it does not. This will cause the sensor to report much larger values than reality.
- Periods without updates extending beyond 2 hours: This causes the statistics sensor to report “unknown”. This scenario is covered fairly clearly in the docs, but the other two are not.
After understanding these issues, I tried a different route and went for a derivative sensor. I set it up based off a 2 hour window and looking at the actual meter reading (as opposed to looking at the rate sensor which is what the statistics sensor was pointed at). This derivative sensor has different issues:
- It only updates upon state changes. So during long periods of no water usage, the derivative remains at whatever the rate was just prior to the water being stopped.
- It will never report a zero derivative on an always-increasing sensor like a meter reading, because there will never be two of the same values for which it to calculate zero slope.
- The sensor is simply calculating the slope between two points: One point 2 hours ago, and one point now. The value it takes from two hours ago is interpolated from the two closest data points (one older than 2 hours, one newer). With a sensor that changes in steps, I’d want to just look at the data point older than 2 hours.
If anyone is still reading at this point: Am I doing something silly? Can it really be this hard to get an accurate calculation of average water usage?
My next plan is going to be to set up an SQL sensor to pull out the water meter reading that is 2 hours old (or more accurately: the newest reading that is older than 2 hours). With that value known, I can make a template sensor for rate (derivative) based on the latest meter reading and that SQL reading, and have it update however often I want (time-based, not state-change-based).
I think I’m just surprised at the fact that HA is set up to not store sensor data if the state hasn’t changed, but then the statistics sensors and derivative sensors do such a poor job of handling that scenario.