How to minimize database growth resulting of frequently changing sensor value

Configuration
1

Hi everybody,
I am measuring my water tank level with a pressure transmitter which is connected to a shelly UNI (adc-input). The transmitter often changes its voltage around 1 time per second, so there is a big amount of data and my database ist growing very fast since I installed the sensor.

A template sensor is used to calculate the water level in percent.

template:
  - sensor:
      - name: "Fuellstand_Zisterne_prozentual"
        unit_of_measurement: "%"
        state: >
          {{ ( states('sensor.shellyuni_xyzxyz_adc') | float | round(3) / 3 * 100) | float | round(0) }}
      - name: "Fuellstand_Zisterne_in_Liter"
        unit_of_measurement: "l"
        state: >
          {{ ( states('sensor.shellyuni_xyzxyz_adc') | float | round(3) / 3 * 5200) | float | round(-1) }}

And a statistic-sensor is smoothing the data (only returns the min-value of the latest 5 minutes)

  - platform: statistics
    name: "Fuellstand_Zisterne_prozentual_geglaettet"
    entity_id: sensor.fuellstand_zisterne_prozentual
    state_characteristic: value_min
    max_age:
      minutes: 5

So far everything is nice and working. Now the problem:
My database is rising very fast since I installed the sensor, this altough I only allow my database to record the statistics-sensor. The shelly-adc (the origin of all sensor data) and the template-sensor are excluded from the record-integration. Same is for the history-integration.

But it seems that the exclusion of the origin-sensor and the template sensor is not working, maybe because they provide the data for the statistic-sensor.

My question is: Is there a better way to reduce/to minimize the measured and recorded data of my pressure transmitter? Indeed it would be enough to have one reading of the water tank level every five minutes, maybe time-pattern triggered template sensors?

2

Have a look here:
Recorder - Home Assistant (home-assistant.io)

3

Yes, I have read this. With the include-integration I have defined the entities which will be recorded. Only the statistic-sensor is recorded. But thats the point: The growth of my database is unbroken.

4

The statistics sensor reads values from the recorder database. It needs sensor.fuellstand_zisterne_prozentual to be recorded as well or it won’t work.

5

That proves what I have assumed. Thank you for your clarification.

6

What about a template with a time_pattern trigger? Just take the raw value every 5 (or whatever) minutes. You won’t get averaging, but at least you won’t be writing to the database every second.

This might also be a good time to plug my Feature Request to allow recorder retention periods per entity, rather than globally. Again, it wouldn’t stop the writes, but it would keep the database from filling up if you could set it to purge that one entity more frequently.

7

I will try the time pattern trigger and report again. And yes, I support your feature request.

1 Like
8

Now I use the time pattern trigger but it does not work like expected.

What I wanted: The sensor fuellstand_zisterne_prozentual should be updated every 5 minutes and should not been updated in between this period.

template:
  - trigger:
    - platform: time_pattern
      minutes: "/5"
  
  - sensor:
      - name: "Fuellstand_Zisterne_prozentual"
        unit_of_measurement: "%"
        state: >
          {{ ( states('sensor.shellyuni_xyzxyz_adc') | float | round(3) / 3 * 100) | float | round(0) }}
      - name: "Fuellstand_Zisterne_in_Liter"
        unit_of_measurement: "l"
        state: >
          {{ ( states('sensor.shellyuni_xyzxyz_adc') | float | round(3) / 3 * 5200) | float | round(-1) }}

What I got: The sensor is updated much more often (see picture).

timepattern trigger
timepattern trigger1024×476 32.1 KB

Maybe fuellstand_zisterne_prozentual is updated all 5 minutes, but obviously also at additional times, most probably triggered by the value changes of its source (but should not do so according to ha documentation). So the question is: How to suppress the sensor updates through value changes of its source but only through the trigger set?