Batched data persistence

Could anybody point me in the right direction for loading historical/batched data into persistence? I’ve got a JSON web service that provides data for the last few hours that I need to poll. I want to make historical values available in OH, I’m currently using RR4J.

The data isn’t necessarily even, or reliable, it comes when it comes in various size groups, but I do get a timestamp with each value, so I should be able to target the historical value - but I don’t know how (or if it’s possible) to write these values into the history for those particular timestamps (and either skip or overwrite timestamps that already have a value).

I appreciate that I might need to switch to InfluxDb or something for this. The other alternative I have is, how feasible is it to plug into a non-OpenHAB instance of InfluxDb, e.g. something I can populate out of band but still have OH use for historical data?


  • Platform information:
    • Hardware: x64
    • OS: Linux 5.14.21
    • Java Runtime Environment: 17.0.10
    • openHAB version: 4.1.1

What’s your use case?

  1. using (and/or showing) that data within openHAB? in rules or graphs?
  2. just “to have it” and/or get some dashboard overviews (not necessarily wihtin openHAB itself)?

ad 1
yes, you would have to save it in an additional persistance like SQL oder influx.
but: I don’t know of a way to change data in the persistence or even write “historic” data on a later date with openHAB tools.
Of course you could INSERT/UPDATE that data in your SQL-databases, which then in turn automagically can be used in openHAB. But you’ll have to write those statements yourself (e.g. knowing the database structure for that

ad 2
you can directly send the data to lets say influx for dashboards there or in grafana - but you’ll have to also use the timestamps for those INSERTs.

1 Like

I’m not sure on the use-case :slight_smile: At the very least dashboarding, but I’m possibly looking at interrogating historical values to make decisions on future changes etc. The update frequency of the data isn’t that great at the moment, so I might not be able to realise that part though.

I haven’t looked into the persistence engines too much and have viewed them as a “black box”, is the idea of having OpenHab talking to a ‘shared’ Influx DB or something a workable approach? OpenHab reads persistence data, I have another process handle inserts.

Sounds like X-Y problem to me
Since you’re unsure in the first place what that would be good for, you better get your use case right before you start looking for a solution how to implement it.

I believe you misunderstand how this is supposed to work. Persistence tracks openHAB item states. If configured correctly it’ll record every change the very moment the item changes so if you have properly setup some OH item to track your external data source, default persistence will allow for charting it. So put your efforts into mirroring your external data into OH items.

However if you’re looking to copy and graph some external data that isn’t available as openHAB items, persistence is the wrong tool for that. It’s persistence, not a general-purpose database interface. Only OH itself should write to it, no inserts into DB from the outside allowed.

PS: you could use the new TimeSeries data type if you really need to get data in.

OH 4.1 introduced the ability for persistence to store and use future values. However, support for that in the UIs and rules has lagged behind and is not yet fully available everywhere.

As part of the support for rules I believe they are implementing a version of the persist action that takes a timestamp to insert data at a given time.

However, none of this works with rrd4j because of the way rrd4j works internally.

Therefore, right now the only way to achieve this is to insert the data using the OH REST API which has an endpoint where you can insert a value with an arbitrary timestamp.

If you format the time series correctly and name your Items in OH correctly you should be able to update the data outside of OH and OH will see it.


oh, that’s great, didn’t know that one. you mean /persistence/items/{itemname}? Does it only “INSERT” or is the wrapper able to UPDATE, if the timestamp is identical (MySQL uses the timestamp as unique key). Swagger docu is not clear on that…

you could use the same tables for openHAB and influxDB/grafana, if you know what you’re doing, yes. But I won’t recommend it. openHAB uses the persistence for restoring after restarts (at least the one defined as “standard persistence service”) and you could mess up things here. Or some major upgrade changes the way persistence is used and you have to rework your use of influxDB.

That’s correct.
What I did while migrating one of my openHAB instances is to just INSERT “old values” into MySQL, but for that you’ll have to know the structure. In the “items”-table, there’s all OH-items and the ID then works for “item0001” (you can define the suffix-id in the JDBC-Persistence configuration, defaults to 4, meaning item0001 until item9999.)
So an

INSERT INTO `openHAB`.`item0001` (`time`, `value`) VALUES (<{time: current_timestamp(3)}>, <{value: }>);

will then create an entry for that timestamp. REST API will do that for you as Rich pointed out.

But: first off try to define your “real” use case, the rest ist just spitballing here! :wink:

1 Like

Good question. I don’t know.

I did some tests.
of course, if you use rrd4j (either by leaving serviceId empty (rrd4j still standard) or by using “rrd4j”) it returns:

  "error": {
    "message": "Persistence service not modifiable: rrd4j",
    "http-code": 400

If you use the same time timestamp, the

  1. first one will get you your 200 OK response => and INSERT the state at the exact time you’ve given
  2. the second one also give you a 200 response => but INSERT the state at the current timestamp and DELETE the one with the historic timestamp.

I don’t know, if that’s expected behaviour, but seems a little “strange”?

That looks like a bug to me. What persistence service did you use for that? It may be specific to one service.

That’s JDBC Persistence MariaDB.

Core only defines an interface to store an item state at a specific time. The specific store method is implemented in the respective persistence services. So my guess is it is not properly implemented in the JDBC service.
The issue may be here: openhab-addons/bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/internal/db/ at 2f7ad5f62e03e396a8604222536bec739d08befc · openhab/openhab-addons · GitHub
It does an sql INSERT, but I am not sure that will work when there is already a value for that key.

To analyze further, switch on debug logging for the JDBC persistence, and log an issue in the addons repository. I can’t analyze further as I don’t use JDBC myself, but that way someone may look at it.

1 Like

did that, thanks for looking into it.
I used timestamp “2024-04-09T06:00:00.00+0200” with item “KNX_DiningWalllight” and “ON/OFF”

first try - “ON”:

2024-04-10 09:52:46.658 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=KNX_DiningWalllight (Type=SwitchItem, State=OFF, Label=Esszimmer Wandlampe, Category=lightbulb, Tags=[Light, Switch], Groups=[PL12DiningLights]) state=ON date=2024-04-09T06:00+02:00
2024-04-10 09:52:46.658 [DEBUG] [persistence.jdbc.internal.dto.ItemVO] - JDBC:ItemVO tableName=item0140; newTableName=null; 
2024-04-10 09:52:46.659 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'KNX_DiningWalllight' as Type 'SWITCHITEM' in 'item0140' with state 'ON'
2024-04-10 09:52:46.659 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: itemState: 'ON'
2024-04-10 09:52:46.660 [DEBUG] [persistence.jdbc.internal.dto.ItemVO] - JDBC:ItemVO setValueTypes dbType=VARCHAR(6); javaType=class java.lang.String;
2024-04-10 09:52:46.660 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: other: itemState: 'ON'
2024-04-10 09:52:46.660 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO item0140 (time, value) VALUES( ?, ? ) ON DUPLICATE KEY UPDATE VALUE= ? timestamp=2024-04-09 06:00:00.0 value='ON'
2024-04-10 09:52:46.675 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'KNX_DiningWalllight' as 'ON' in SQL database at Wed Apr 10 09:52:46 CEST 2024 in 17 ms.


second try - “OFF”:

2024-04-10 10:00:54.168 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=KNX_DiningWalllight (Type=SwitchItem, State=OFF, Label=Esszimmer Wandlampe, Category=lightbulb, Tags=[Light, Switch], Groups=[PL12DiningLights]) state=OFF date=2024-04-09T06:00+02:00
2024-04-10 10:00:54.169 [DEBUG] [persistence.jdbc.internal.dto.ItemVO] - JDBC:ItemVO tableName=item0140; newTableName=null; 
2024-04-10 10:00:54.169 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'KNX_DiningWalllight' as Type 'SWITCHITEM' in 'item0140' with state 'OFF'
2024-04-10 10:00:54.170 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: itemState: 'OFF'
2024-04-10 10:00:54.170 [DEBUG] [persistence.jdbc.internal.dto.ItemVO] - JDBC:ItemVO setValueTypes dbType=VARCHAR(6); javaType=class java.lang.String;
2024-04-10 10:00:54.170 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: other: itemState: 'OFF'
2024-04-10 10:00:54.171 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO item0140 (time, value) VALUES( ?, ? ) ON DUPLICATE KEY UPDATE VALUE= ? timestamp=2024-04-09 06:00:00.0 value='OFF'
2024-04-10 10:00:54.178 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'KNX_DiningWalllight' as 'OFF' in SQL database at Wed Apr 10 10:00:54 CEST 2024 in 10 ms.


I also tried to use the SQL-Statement in mariaDB without the wrapper:

10:05:04	INSERT INTO openHABMain.item0140 (time, value) VALUES('2024-04-09 06:00:00.0', 'OFF') ON DUPLICATE KEY UPDATE VALUE= 'OFF'	2 row(s) affected	0.000 sec

and the result is just the same: DELETEing the original row and INSERTing just “OFF” without a timestamp. or rather two updates:

I think the SQL-Statement does not work with MariaDB/MySQL-DB, because of the declaration of the table "ON UPDATE CURRENT_TIMESTAMP(3) ".

Good analysis, so you should create an issue on the addons repo with this information.

1 Like

I created a BUG here:

It could have an impact on time series, if they’re updated in the persistence… I did not have a look into that yet, but possibly that’s also an issue.

For readers of this thread, I believe the issue was identified: