Problems with persistence queries

Add-ons Persistence Services
1

Hi i am running OH2 in Win 10 with Maria DB for persistence.
My question is how i can retrieve values beetween two dates.
For example i want to know the average values of an item for a certain period (from 10.09.2019 to 15.09.2019)
I know the possibility to query with “item.averageSince(???)”
But this returns the values from a certain point in time until now.
Assuming that i query in a rule

logInfo("TestRule", "averageSince" + item.averageSince(now.withTimeAtStartOfDay.minusDays(1)))

In the logs i can see the Query:

JDBC::doGetHistItemFilterQuery sql=SELECT time, value FROM Item100 WHERE TIME>'2019-09-17 00:00:00' ORDER BY time ASC

The result seems to be correct.
But now i want to have the average values from

TIME>'2019-09-17 00:00:00' and TIME<'2019-09-18 00:00:00'

I will appreciate any help or advice

2

IMHO such querries are not build into openHAB, so you can’t get such data directly. However using the REST API you can do querries for data from time1 to time2. Calculaling the average has to be done you.

3

I am not familia with REST API .
Maybe there is a way to realize it with exec bindíng?

4

Do you have the “REST Documentation” installed (via PaperUI-Adons-Misc)? Then the REST-API UI will show on the dashboard. Using this UI the required syntax for CURL or Request URL wll be shown.

Note: If you are on a more recent version (snapshot and maybe Milestone) the way to install the REST Documention changed, but I don’t have the specifics on hand.

5

Example:
Your persisted item is named “MyItem”, your persistence service is “rrd4j”, the name of system running openhab " MyServer". Using the "/persistence/items/{itemname}"tab on the REST API UI you would get shown the correct CURL and URL syntax and you also get shown the return of such request (using the “try it out” button)
In order to get the values persisted from 1. September 2019 00:00:00 Z up to 1.September 2019 23:59:59 Z the CURL command is:

curl -X GET --header “Accept: application/json” “http://MyServer:8080/rest/persistence/items/MyItem?serviceId=rrd4j&starttime=2019-09-01T00%3A00%3A00.000Z&endtime=2019-09-01T23%3A59%3A59.000Z”

The URL is:
http://MyServer:8080/rest/persistence/items/MyItem?serviceId=rrd4j&starttime=2019-09-01T00%3A00%3A00.000Z&endtime=2019-09-01T23%3A59%3A59.000Z

The return would look like:

{"name":"MyItem","datapoints":"96","data":[{"time":1567296000000,"state":"1.309"},{"time":1567296900000,"state":"1.309"},......
That JSON String needs to be parsed for all “state” values.

6

Hi Opus,
i tried your suggestion and it wors well in a Browser.
I can get the results in my browser but i didnt get them on my OH.

But i think the better solution for me are the MYSQL statements
I have to Query some values and get one result from the Database.
Sometimes i have to write on it.

I Continue to try to find a solution