Influxdb with openhab problem with aggregate / downsampling / continuous query

Good Evening @all,

i have a problem with downsamling of information. My system: influxdb 1.7 on raspberry pi 4 with openhab 2.5
I can read out all data points, so i am sure that all the data i have stored the last months, are available. My problem that i want to solve is: request to my influxdb were answered with “wrong” values. I think this has something to do with maximum agregate or downsampling from data for continuous query. I store some data every minute, some other data every 5 minute. And then I try to get the absolute maximum, minimum and average values for the last 365 days. Problem: after a few days the values are no longer correct, although they are available in the DB. Can anyone help me ?

Can you share you continuous query or the query you are running? also provide current and expected output

Missing Information: InfluxDB Version: 1.7.10

Maybe I’ll start again with the fact that I only used InfluxDB to the extent that I installed it with openhab. I did not make any configurations (except password, create DB etc.).

What i do actual:
Store data from my (selfmade) weather station (wind, temperature, humididy…) Some values are stored every minute (Wind/guest speed) the rest every 5min. So openhab sends the data to the DB.
I am not only interested in what is happening in the weather, but also what has been going on in the last 24 hours and the last year. Therefore I get the values with an OH rule:
e.g. 1. get maximum temperature value from last 24 hours.
e.g. 2 get maximum temperature of last 365 days
(same with windspeed)
So, when i do it like in the example, the values that i get back is today 14°C, but i now, that a few days ago
we have 17°C. When i change the 365 days in 5 days, the value which is comming back is the (right) 17°C.
Same with windspeed…

as i said before: i am a total beginner: what am I doing wrong ?

This makes no sense, can you share your query?

Yes that is no problem:

So, i can ask in influxDB:

SELECT MAX(*) FROM "Wetterstation_Boengeschwindigkeit"

and i get a value:
96.4798… So that seems to me right, that was the highest value i know i measure.

And when i ask in openhab2:

 rule "Schnellste Windboe im letzten Jahr"

    when 
        Item Wetterstation_Boengeschwindigkeit changed
    then 
        Wind_max_stunde.postUpdate(Wetterstation_Boengeschwindigkeit.maximumSince(now.minusDays(365), "influxdb").state)
    end 

i get the value: 74.56

So, this value must be wrong.

Same with temperature… get the right value in the influxdb query, but the wrong one in openhab.
But when i change from 365 days to 2-3-4-5, the value in that timerange is the right one.

Sorry but I don’t know openHAB, I’ve looked around a bit for similar issues on the openHUB community but there are just too many things I don’t know.

If you don’t get other answers here, try on the openHAB community since (to me) this problem seems more related to that, in fact, Influx itself returns the correct values when queried directly

O.k., PURE luck, i found the solution.

in the file:
influxdb.conf

is a http section and in this section is a row:
max-row-limit = 10000

so i change it to 1.000.000 and now i get the right values.

Just a note: This is highly inefficient!
So what you did is the following: In openhab you are using influxdb for persistence and it writes all changes from Wetterstation_Boengeschwindigkeit into the database. Openhabs internal object do have a history, that can be queried, but it appears that they simply retrieve ALL of the entries in InfluxDB and then manually filter through them in Openhab. That is why increasing the max-row-limit helps.
This totally ignores the facilities of using InfluxDB and applies a brute-force method.
So what you can do instead is to run an HTTP query and parse the resulting JSON response.

rule "Query max humidity"
when
    Time cron "0 * * * * ?"
then
    //Set this to your influxdb URL, the par before @ are use:password if needed
    val url="http://USER:PASSWORD@localhost:8086/query"
    val queryRaw='SELECT max("value") FROM "s_ESP32_AirQualityKitchen_default_BME680_Humidity" WHERE time > now()-24h'

    //No changes needed here
    val queryEncoded="db=openhab_db&q="+java.net.URLEncoder.encode(queryRaw, "UTF-8")
    val contentType="application/x-www-form-urlencoded; charset=utf-8"
    val response=sendHttpPostRequest(url, contentType, queryEncoded, 500)
    val String result=transform("JSONPATH", "$.results[0].series[0].values[0][1]", response)
    logInfo("maxhumidty", "Response was:"+response+" Result:"+result)
end

That is already quite a bit better, but even better is when you then create a true continous query: https://docs.influxdata.com/influxdb/v1.7/query_language/continuous_queries/

Hope this helps,
Karsten