Recorder: possible to only write values to database if condition is met?

Hello everyone,
I am currently using tasmota to filter broadcasts pf dta that is irrelevant (e.g. power consumption change of 1 W).
However, I also have devices with stock firmware. Does anybody know if it is possible to filter in Home Assistant rather than the device?

I would like to set rules like

 If Power > 100 W & PowerDelta > 10 W Then write to DB
 ElseIf Power < 10 W & Power < 100 W & PowerDelta > 5 W Then write to DB

Hope someone can help me out here :slight_smile:

Thank you
Alex

You can only exclude whole entities, you cannot exclude some of the state changes of an entity.

So what you’d have to do here is tell recorder to ignore the sensor created by those devices with stock firmware entirely. And maybe change the entity ID by adding _unfiltered or something so you know these sensors should be ignored.

Then create a new sensor which wraps each unfiltered one and removes the noise by conditionally ignoring state changes. A filter sensor might work. Or if that doesn’t give enough control then probably a template sensor. These ones you do record history for and use throughout HA in place of the unfiltered ones.

2 Likes

Thank you for your suggestion, Mike!

But I would need to create one sensor per entity then, correct?
I cannot create one sensor for a list of entities (e.g. using wildcards like sensor.shelly* ?

Idea would be to automate this process as much as possible.

Filtering the entities would not be difficult as I am currently filtering out everything except what I explicitely permit. But e.g. Shelly devices have dta I want but I do not need updates of constant values every 5 seconds.

Not really.

You can kind of do this with a template sensor if you basically add one attribute per sensor. Like let’s say you have a sensors like sensor.shelly_power and sensor.shelly_2_power. Instead of making a filtered sensor for each you could have one template sensor like this (oversimplifying the filtering logic, hopefully you get the idea):

name: Shelly power filtered
state: ignore_me # The actual state of this sensor is totally irrelevant
attributes:
  sensor.shelly_power: >-
    {% set p = states('sensor.shelly_power') | float(0) %}
    {{ p if p > 5 and p < 100 else this.attributes['sensor.shelly_power'] }}
  sensor.shelly_2_power: >-
    {% set p = states('sensor.shelly_2_power') | float(0) %}
    {{ p if p > 5 and p < 100 else this.attributes['sensor.shelly_2_power'] }}

But this is probably not going to work for you. The two biggest issues are:

  1. It’s going to create a ton of DB noise since every time any sensor updates it records a state change for this sensor. And that state change includes the current values of all of them even though most didn’t change
  2. You can’t really use this in the UI at all. Can’t display a graph, see the history, use it in the energy dashboard, etc.

Really the only way this could possibly work is if you were only using this information in SQL queries directly on the HA DB or reporting it out to Influx (or some other external data aggregation service) and only looking at it there. Then the information would be available it just would be weird. And brutally taxing on your DB and hard drive since its writing out huge state change events with duplicate data all the time.

And I can’t really think of any better workaround either. Think making a sensor per sensor is really the only viable option in the product currently.

1 Like

Thank you very, very much, Mike!

Creating DB noise is exactly what I am trying to prevent. After adding a Shelly 3EM with original firmware my DB size double within 3 weeks. So I really want to filter all the noise and redundant information.

And I of course need to be able to access it from “anywhere inside” HA. This has always been problematic in HA. Data access and data recording has always been sub-optimal.

Maybe I will try to flash tasmota on all Shelly devices, but of course that is an external workaround that is then needed per device rather than centrally. And it is not possible for all devices. Just some. And Shelly with tasmota is still quite buggy.

I think you make a good case for yet another Feature Request regarding Recorder. It would be great to have some sort of filtering on what gets recorded.

Maybe it could include the ability to limit the number of changes over some time period. Or a range within which values are to be recorded (or not.) Or maybe the amount a value needs to change before being recorded. I have temperature sensors which report in tenths of a degree. Maybe only recording whole degree changes would be sufficient (and save writes to the database.)

Creating templates for each entity, just to work around the limitations of Recorder, is less than ideal. There are other Recorder FRs like this and this. With all the other great things the HA dev team is doing, it would be nice to see Recorder get some love.

Yes, in tasmota those features are all implemented.
Rules - Tasmota let you filter.
Commands - Tasmota let’s you define things like PowerDelta.

And for th tougher cases, Berry - Tasmota let`s you do anything you want.

I have Xiaomi Mijia Humidity & Temperature sensors that report values every 10 sec. Totally stupid. But I filter them and only get 5-10 per HOUR now.

Unfortunately my experience with Home Assistant over the last two years have shown that Feature Request or Month of WTH are a waste of everybody’s time. The people taking the time to write the request, explain and propose solutions, the people helping by adding optimizations or help find the best approach and vote…
Nothing ever comes of them.
Even if by some kind of miracle a dev sees it, nothing will happen. Even if they state it is a simple fix, nothing will happen. Even if people create PRs, they will just sit on Github until they are stale and will never come to life.
Year or the voice just proves this even more.

Anyway, the only way this (or anything else) will change is if someone more skilled than me writes an integration.

Anyway, I am thinking about whether it would make sense to try and write a script that searches the SQL database and cleans it. But that would be a dirty fix that will only work for certain databases.

I will need to check again who was in charge of the HA database. BEcause that was one of the very few discussions were the person in charge really did seem to be highly interested in optimizing it and it was implemented very quickly.

1 Like

to reduce the DB Noise, I would really dig into your entities and decide, which entities you want to store and which not.
For example - I’ve exluded all “uptime” entities and update entities, because this is no information I would need any kind of statistics or history.

you could also create templates with these kind of rules - as explained - write them into your recorder and exclude the source sensors…
And as an addition of that, decide, for how long you want to store your data…

Basically, your Database should grow to a certain size - and then remain around that level (because it would remove data with a certain age) - until you add new integrations etc.

Anything else will most probably depend on your type of setup.
I am using a dedicated server with MariaDB for recording - that does allow me to allocate the space for the Database and probably also to do my own queries for custom dashboards, etc.

I have excluded everything I can.
I want simply the energy/power consumption for the Shelly devices. But they report on every single change. And especially the 3EM that monitors the main power line, updates every few seconds because one of the values changes. Of course if one changes (i.e. Phase 1 current), all get updated (Phase 1, 2 & 3, voltage, current, etc. etc.).
So filtering with the basic recorder filters helps reduce to 3 entities, but those 3 have hundreds of datapoints per hour.

The Xiaomi temperature and humidity (key values I want to track) reports every 10 seconds.

Nothing you can do without filtering in tasmota or, ideally, HA.

That would be a great workaround. Unfortunately, as far as I recall the SQLite Web add-on doesn’t support the DELETE statement in SQL. I’d love to be proven wrong, but I think I tried once.

Your other observations are spot on. This forum, and the WTH’s and FR’s posted here, are often ignored by the devs. Or worse, ideas are met with defensive and dismissive retorts. There’s a real “Us vs. Them” relationship developing with users which I’ve seen before, and it’s never good for the project. I am not trying to denigrate anyone. I only say this because I want HA to succeed.

I will admit that some users have posted poorly thought out or downright silly ideas. Some do deserve a dismissive response. But as you say, other FRs have been carefully considered and composed, with the aim of improving the experience for all HA users, not just the “chosen few” who live and breath this stuff 24 hours a day and have complex hardware test labs set up to do development on.

1 Like

I was thinking more along the lines of command line script or, more likely, phpMyAdmin script. But I am a bit concerned about
a) processing time to search and iteratively compare through the entire db
b) running the script in normal operation and subsequent risk of db corruption

It is always better to filter before storing the data rather than filter and delete after storing the data.

1 Like

Btw., this is the impact of a single, unfiltered device on a system that otherwise uses tasmota’s PowerDelta:

1 Like

Yup. The weaknesses of Recorder are HA’s best kept secret. I’ve said it before: We’re setting beginner users up for failure by recording everything by default, and giving no way in the UI to set the purge duration, or filter or exclude, at the time an entity is added.

1 Like