Sensor Value for a specific Time Range from InfluxDB

[slarrain (Santiago Larrain)]

I bought a couple of Ecowitt Wh51 to measure my garden’s soil humidity. I have been moving them around different parts of the garden and, of course, I have been gathering different measurements depending on the position.

But some of the positions where from several days ago, so HA can’t show them to me (too far back). Thankfully, I have connected my HA to InfluxDB, so I get to see the measurements for longer periods of time.

This is a Grafana Graph where you can see the moment I switched positions on the sensor:

What I want, is to be able to get the mean, median or some other aggregate function for the period of time that was in a certain position. I can manually put on the time range for the position. But I can’t get the value I want.

I tried something like this:

sensor:
  - platform: influxdb
    api_version: 2
    ssl: false
    host: 192.168.1.200
    port: 8086
    token: !secret influxdb_token
    organization: !secret influxdb_organization
    bucket: HomeAssistant
    queries_flux:
      - name: "Soil Moisture 1 A"
        query: >
            from(bucket: "HomeAssistant")
            |> range(start: 2022-08-06T00:00:00Z, stop: 2022-08-10T15:30:00Z)
            |> filter(fn: (r) => r["friendly_name"] == "Soil Moisture 1")
            |> filter(fn: (r) => r["_field"] == "value")
            |> filter(fn: (r) => r["_measurement"] == "%")
            |> filter(fn: (r) => r["domain"] == "sensor")
            |> filter(fn: (r) => r["entity_id"] == "soil_moisture_1")
            |> filter(fn: (r) => r["source"] == "HA")
            |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
            |> mean()
            |> yield(name: "mean")

Which on InfluxDB gives me the mean Humidity % of that period (precisely what I want), but I get "unknown" state on Home Assistant, probably because the value if from a too far back date.

My ultimate goal is to be able to map different parts of my garden with their respective Humidity values (even if they are from weeks ago, since it doesn’t change that often). Something along this lines:

But the values is showing now are the current values from the current positions (which is OK, but I would like to add different values for different positions based on the past values of the sensor).

I don’t know if getting the value back from InfluxDB is the right way or not. I’m very new to Home Assistant and is awesome, but there is so much to learn.

Thanks in advance!