Influxdb Null values between datapoint changes

hey folks,

I am pretty new to openhab so I am sorry if this question was already asked.
At least I couldn’t find any existing topic for it. Maybe I used the wrong keywords.

Anyways… here is my problem.

I am using openhab 3.2.0 and installed influxdb as a persistance service and grafana to visulize the values.
All these services run in containers on my raspberry.

To setup the influxdb persistance I simply used the openhab web interface and configured the credentials, etc.

Now I got following behaviour:
I see every 15 seconds an empty entry to the influxdb. Values are only stored if it changed.

2022-01-16 08:27:45
2022-01-16 08:28:00
2022-01-16 08:28:15
2022-01-16 08:28:30      20.1
2022-01-16 08:28:45
2022-01-16 08:29:00
2022-01-16 08:29:15

Where do I find the settings for the persistance service? The UI offers only the basic configuration like user and host.

Would be nice to reduce the interval of the stored values and store them even they did not change.

Thanks for you help!


The docs offer some clues, what is persisted when - the “strategy” - is controlled by xxx.persist files (not editable from GUI yet)

Hey rossko,

Thanks for the link. I dig into it…
The folder is empty right now. So the persistence config from the UI is not stored there.
Is the config file as soon as it is present active or do I have to activate the config file somewhere?

That’s right. The absence of any influxdb.persist file is what invokes the default persist strategy. There is no existing file to edit your own, you must create from scratch.

Debatable. It certainly used to require a service restart to implement new xxx.persist changes in OH2. This may not be the same for all persistence services.

Separate to that, is whether adding/removing member Items to a Group is taken into account in-flight. Don’t know.

Making some progress here.
I created the influxdb.persistent file and added it t the container

Thats the file for now:

Strategies {
        everyMinute : "0 * * * * ? *"

        // if no strategy is specified for an Item entry below, the default list will be used
       default = everyChange

Items {
        * : strategy = everyMinute, restoreOnStartup

To keep it simple for the start. I just want to log every item every minute,
This actually works, I get the values even if they did not change.
But… the null values are still logged to the influx db? Now its even every 2 seconds.

2022-01-17 23:44:56
2022-01-17 23:44:58
2022-01-17 23:45:00    20.5
2022-01-17 23:45:02
2022-01-17 23:45:04
2022-01-17 23:45:06

What do I miss here?

You’ve not mentioned trying any kind of restart. This may be necessary to stop it doing what it was already doing.

Where do you view this data? What does the database query look like? When querying Influxdb you can choose to fill the gaps in the data with null values in regular intervals. This doesn’t mean that these are entries in the database itself.

I did restart the container of course. There was an entry in the openhab.log that the file is recognized.

I view the data in grafana and used the query builder. Can’t the tell what the query exactly looks like right now, but I can post it tonightm when I am back at home. Tbh I did not really look into InfluxQL for now :sweat_smile:.

Well, then the default query looks something like:

SELECT mean() FROM <YOUR_ITEM> WHERE $__timeFilter GROUP BY time($__interval) fill(null)

Not entirely sure about the syntax, I use Influxdb 2, which uses a different query language, but should be something like this. Let’s break it down:

SELECT mean() FROM <YOUR_ITEM>: The basic select statement. mean() means that it calculates the mean of the values if there are multiple within the same time window (2 seconds in your case, see more below)

WHERE $__timeFilter: $__timeFilter is a variable in grafana which gets expanded to the current start and end times of the graph, so it only includes the relevant values.

GROUP BY time($__interval): This tells influxdb to group the values into specified time windows instead of using the exact timestamps. If multiple values goes into the same window, an aggregation function is used to calculate the value (mean in this case) $__interval is a grafana variable that gets expanded to a proper resolution for the graph (the maximum number of points that can fit).

fill(null): This tells influxdb what to do with the time windows that doesn’t have any values in them, in this case return a null value (also the default if the fill-argument is omitted). Other possible values is none which omits the time slot from the result, linear, which interpolates values, or previous which fills with the previous value up until a different value is included. Generally, I have found that it’s better to let grafana handle these things (i.e use fill(none)).

Okay…. Stupid me :sweat_smile:
I guess I was to much into regular SQL. I assumed when there is no data the query won’t display any data.
After taking a look into the query builder you’re right. Fill was set to null and the Intervall was the $__interval variable. For now I have now idea where grafana took the 2 seconds from but adjusting it to one minute the time series looks just fine.

Obviously I have to take some time to look into influxdb 101 or something. It’s more different to SQL then I thought. Thanks for your patience I guess I can figure out the rest by my self =)

Basically, grafana calculates it as the total timeframe of the graph divided by the graph’s width in pixels (or something like that)