Is it possible to insert into DB only if the item value differs from previous value?

,
1

I’ve running OH (3.4.3) and since the beginning of the year with InfluxDB v2.6.1. Everything works perfect.

I’m currently developing a small application which will allow me to graphically change specific values in the InfluxDB (e.g. wrong measurements at specific times) or delete specific (e.g wrong or redundant measurements) points in InfluxDB with the possibility to filter for specific values.

I’m sure, you all know this annoying problem when an item value in a line chart goes out of the way in one direction and the entire chart scaling no longer fits and the chart gets useless (e.g. in some of my temperature data, I sometimes have values of -100, once or twice a year - for what reason ever). I want to be manually able to correct these wrong values from time to time.

While I’ve testing my tool with some items, I’ve made the following observation. There a some sensor data which sends (sometimes - rare event) many of the same measurement data very fast (sometimes 10 times per second) for a period of time. There is not really a valuable information to store these, always the same, data in the DB. It only costs space. If found a (wrong behaving zigbee temperature sensor) which inserted about 80,000 times the same (wrong) value in 24h.

I wonder if the persistence service in the current OH version is able to activate an option that will only insert data into the DB, if it’s different from the previous item value?

If not, this would be a very useful option to save database storage (nearly) without loosing useful information.

2

Doesn’t this already exist with the everyChange persistence strategy? You can pick either:

  • everyChange: persist the Item state whenever its state has changed
  • everyUpdate: persist the Item state whenever its state has been updated, even if it did not change

everyChange seems like what you want. Are you already using that? Or do you use everyUpdate?

From: Persistence | openHAB

3

OMG, sometime it’s so simple… thanks a lot for this hint. I’ve searched all the InfluxDB and OH-InfluxDB specific documentation but totally forgot the general DB independent persistence strategy settings in OH. It’s so long ago since I’ve created/changed this settings. Sorry about this unnecessary question…

4

Wouldn’t it be better to prevent that erroneous value from being saved in the first place?

In OH 4 there are some new persistence filters you’ll be able to apply as well as a profile I think to handle this case. In OH 3 you can apply a SCRIPT transform to test if the value is reasonable and return the old value (or UNDEF) if it’s not, effectively filtering out the bogus reading.

Another approach is to exclude the Item from persistence and create a rule that triggers when the Item changes. In that rule you can test to see if the value is reasonable and only if so call .persist on the Item which lets you “manually” save values to the database independent of the persistence strategy.

5

Your are absolutely right, it would be the best solution to not save the wrong values in the database at all.

Good to know that it would be possible to add script transformations (and even profiles in OH4) before the data is stored in the persistence.

But there are situations where it’s hard to automatically predict (maybe with statistical deviation rules or some AI algorithms) if a value is really wrong. I have the following current situation e.g. with temperatures.

OH-chart
OH-chart1364×798 36.9 KB

As you can see, some of my temperature sensor sending wrong data from time to time (for what reason ever), in this case 0 degree Celsius (these are all Zigbee based sensors manufacturer “0x1037”, modelId “lumi.weather”).

As a human, it’s easily seen in the chart that these 0 Celsius values must be wrong in this context - but 0 Celsius may be correct in winter time.

I think persistence rules can help a lot to prevent many of the wrong values stored to the DB. But if it happens (like in my example), it’s hard to get rid of them later.

As far as I know, neither the OH GUI, nor the InfluxDB Web GUI offer a simple way to do this (at least I was not able to find such) - but the InfluxDB API has these features. That’s the reason why I’ve implemented a simple edit/delete tool just to be able to get rid of or change these bad values and have nice scaling charts again (in OH, InfluxDB, Grafana).
Once I had just one -100 degree Celsius value in my chart of 1 year which ruins the whole “year chart” view. And it’s nice to have a way to get rid of this just one wrong value to get a useful chart again.

I know, it’s a boring and stupid way to correct these values manually but I don’t know how to do it 100% automatically as long as sensors may send wrong data…

6

True but it’s not reasonable to drop 45 degrees in a minute. Based on the cart you are getting a reading at a constant rate (every minute?). Let’s say that it’s unreasonable for the temp to change in either direction more than 5° between readings. Then you can just check the previous Item state with the new proposed state and reject it if the absolute value of the delta is more than 5.

Even if the sensor throws an invalid reading that gets past this filter, your chart won’t get messed up.

You could do a statistical calculation but I don’t think that’s necessary.

(function(data) {
  if(items.MyItem.isUninitialized) return data;
  if(Math.abs(parseFloat(data) - items.MyItem.numericState) > 5)  return "UNDEF";
  return data;
})(input)

The above is JS Scripting using the SCRIPT transform. There are ways to make it more flexible (e.g. we can pass in the Item name as an argument) but I’m presenting it as straight forward as possible.

There is a lot in those few lines though so let me explain it.

If the Item is NULL or UNDEF the transform will return “UNDEF”. The transform should never be called when it’s “NULL” because that’s the initial state and bindings won’t update an Item to NULL usually. Therefore, if the Item is UNDEF we return the new reading. That ensures that the transform never skips more than one reading. Even if for some reason the temp did change that much over a minute, we will at most lose a single valid reading (e.g. maybe OH was offline and the temp changed a lot while it was offline).

The second line sees if the change is greater than 5. It’s constructed in a way that it should work with or without units of measurement on the Item. If the delta is more than 5, we assume it’s an erroneous reading and throw it out by returning UNDEF. UNDEF doesn’t get saved to persistence. Because it’s UNDEF, the next reading will be accepted no matter what.

Finally, if the delta is less than or equal to 5 we return the sensor reading unchanged.

One could make this completely generic by using the URL encoded arguments passing the JS Scripting supports. Given SCRIPT(graaljs:filterBadTemps.script?item=MyTempItemName,threshold=5 (OH 3) or JS(filterBadTemps.js?item=MyTempItemName,threshold=5 (OH 4) (note the syntax to call a transformation is different for some bindings like MQTT or HTML) the above can be changed to

(function(data) {
  if(items[item].isUninitialized) return data;
  if(Math.abs(parseFloat(data) - items[item].numericState) > parseFloat(threshold))  return "UNDEF";
  return data;
})(input)

That lets you reuse this one transformation for all of your sensor Items and set a custom threshold for each one.

7

Wow, that’s awesome!

Thanks a lot for this detailed explanation and the description of some of the “under the hood” knowledge how OH handles these UNDEF and NULL situation and what are the default (or usual) behavior. Also this URL encoded script arguments is a nice feature which I was not aware of.

So I definitely will try to implement and test this generic implementation for my temperature sensors. These gives me the most wrong values, other sensors very rarely have these problems. Your are right, the price to loose just one correct reading as a worst case, can easily accepted to achieve nearly 100% filtering of bad values!

Really appreciate your help and knowledge!

8

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.