Query: When was the last time it started to rain?

Fluxlang
1

Hi,

I have a table which stores the current raining state.
If it rains the _value is 1, if it is dry the value is 0.

E.g:

_value    _time
0             2022-09-06T11:00:00.000Z
1             2022-09-06T10:00:00.000Z
1             2022-09-06T09:00:00.000Z  <- 
0             2022-09-06T08:00:00.000Z

I want to get as result 2022-09-06T09:00:00.000Z, when it started to rain.

Do you have any idea how to write that query?

Default Query:

from(bucket: "ESP32-Multi-Sensor")
  |> range(start: -30d)
  |> filter(fn: (r) => r["_measurement"] == "data")
  |> filter(fn: (r) => r["_field"] == "raining_now")
  |> keep(columns: ["_time", "_value"])
  |> yield(name: "rain")

Thx a lot

2

@Guido_Berger If there is only one state change in the query, it’s pretty simple, but if there are multiple (starts and stops raining multiple times), it becomes a little more complicated.

Single state change

from(bucket: "ESP32-Multi-Sensor")
  |> range(start: -30d)
  |> filter(fn: (r) => r["_measurement"] == "data")
  |> filter(fn: (r) => r["_field"] == "raining_now")
  |> filter(fn: (r) => r["_value"] == 1)
  |> first()
  |> keep(columns: ["_time", "_value"])