ZWave + Persistence + Polling Temperature

Setup, Configuration and Use Beginners
1

My goal here is to collect the temperature from a WAPIRZ-01 Motion Detector into a persistence database, so that I can then use the ‘timeline’ widget in HABPanel to view the temperature in the room over time.

I added the sensor to .items:

 Number Office_Temperature "Temperature [%.1f C]" { channel = "zwave:device:fccca473:node8:sensor_temperature" }

I configured persistence in /opt/openhab/conf/persistence/sqlite.persist:

Strategies {
    everyMinute : "0 * * * * ?"
    everyHour   : "0 0 * * * ?"
    everyDay    : "0 0 0 * * ?"
    default = everyChange
}

Items {
    Office_Temperature : strategy = everyMinute
}

I setup SQLite in /opt/openhab/conf/services/jdbc.cfg:

url=jdbc:sqlite:./openhabPersistence.db

I can see the database file has been created and there is 1 table in it. But I’m not getting any data stored. I’m watching the console logs, I can see events like this:

02:11:12.441 [INFO ] [smarthome.event.ItemStateChangedEvent] - Office_Temperature changed from 80 to 78

But again, nothing stored in the DB. Did I configure something wrong? I’ve enabled all the DEBUG logging for everything I can see related to JDBC and persistence and there’s no activity.

What am I missing? Thanks!

2

Persistence also requires a binding. The various protocols are not all baked into the main OH code. The mere presence of the jdbc.cfg is not sufficient to tell OH you want to activate this persistence protocol.

Have you installed the appropriate JDBC Persistence binding?

3

Yes?

openhab> bundle:list  | grep  -i sqlite
237 │ Active │  80 │ 3.16.1                  │ SQLite JDBC

I can see queries into the persistence db:

02:26:02.211 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::query queryString = SELECT time, value FROM item0002 WHERE TIME>'2020-09-09 02:26:02' AND TIME<'2020-09-09 02:26:02' ORDER BY time ASC LIMIT 0,1
02:26:02.211 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetHistItemFilterQuery sql=SELECT time, value FROM item0002 WHERE TIME>'2020-09-09 02:26:02' AND TIME<'2020-09-09 02:26:02' ORDER BY time ASC LIMIT 0,1
02:26:02.211 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::query: query for Office_Temperature returned 0 rows in 1 ms

But those queries don’t make any sense, TIME>X AND TIME<X ??
Also, there’s no data being saved to the DB. This is problem #1.

4

Yes, that looks right.

There are some reports of sqlite persistence not writing data when there are overlapping item definitions. For example:

Have you looked for some potentially conflicting items?

I believe that there should be at least two tables in a persistence db, one that maps the openhab item name to a generic item## title and one that is a table with that item## as the title that holds the time and value data. Given that you are only seeing 1 table in the db, it seems likely that there’s some reason the binding hasn’t registered your item as actually linked to persistence.

Also, if you try to add some other, unrelated, item to the persistence rules, does that item data get properly written?

5

I looked again at the db file. There are 2 tables: ‘items’ and ‘item0001’. There’s data in the items table:

sqlite> select *  from items;
1|Office_Temperature

But empty item0001 table.

In fact, I updated the sqlite.persist file to include everything:

Items {
    Office_Temperature : strategy = everyMinute
    *: strategy = everyChange
}

and I can turn lights on/off, open/close doors, but nothing being written to the DB.

I might look into a MySQL docker next then if sqlite is buggy. Shame.

6

I discovered the solution to problem #1, though not sure why it is the solution. Seems I had the file named wrong. It should not have been named “sqlite.persist”, it should have been named “jdbc.persist”. I tried this because it dawned on me that I’m using SQLite via JDBC, and not native SQLite.

Problem #2. How do I “poll” the temperature from the device? Every minute, as defined, it’s trying to store the temp. But the temp only reports when there’s a change it seems. How can I poll and request the temp every minute? or maybe ever 10m?

7

Normally you can configure the wakeup time and often also a threshold in the things settings.
If you want to report every minute you should configure this there.

But as this is a battery driven device you should not do so as battery would be gone within very short time.
But why to persist every minute if there is no change?

1 Like
8

That is sometimes useful for simple charting, creating a regular data time series.

Of course it is not necessary to actually update the data every minute, just record it every minute.

For usual room temperatures there isn’t really much point getting readings every minute, it doesn’t change that fast.

9

I have charts displayed by Grafana and most items persisting “every change” only.
Grafana draws simple lines between data points, so if there is no change I have a horizontal line, until value changes.
But I agree, there are for sure use cases where someone needs data points recorded e.g. each minute, even there is no change.

10

@chris4789 So I can change this setting on the device to report more frequently, rather than something in openHab to poll every so often? Yes, 1m is probably too frequent for room temperature.

11

You can’t poll a sleeping battery device, so yes it is the device wake-up period that you must configure.

12

Some devices offer a threshold when to report a change.
I have here a Fibaro smoke sensor which has a threshold I can adjust:

grafik
grafik762×316 16.3 KB

But I guess it sends an update also just at wake up time.

There are a few z-wave devices having “Flirs” which means the device is sleeping just for a very short time.
I have a few Spirit TRVs with Flirs, but most of the time they do not report correctly.