0

I have a table in my InfluxDB for a parking sensor which sends a state for occupied (1) and vacant (9). Now I want to create a query which shows me the time period between the status changes so that I can create a report for the time the parkingslot was occupied and free. The data is generated by the parking sensor and inserted via node-red into the influxdb. The data is located in InfluxDB version 1.8.10 on Ubuntu 20.04. The table data has actually the following structure:

name: parkinginfo | time | statusnumbered | | -------- | -------------- | | 1646302488500839186 | 1 | | 1646302488500203666 | 1 | | 1646302488499932866 | 2 | | 1646302488499826263 | 1 |

statusnumbered: 1 = vacant, 2 = occupied

Can someone help me for creating a query for this? Thanks!

derw0lf
  • 1

1 Answers1

0

It is feasible to report the total duration for a given state in InfluxDB but only in Flux not InfluxQL (easily). You could:

  1. Enable Flux in v1.8 with the configuration change here

  2. Sample Flux could be:

    from(bucket: "yourDatabaseName/autogen") |> range(start: 2022-03-03T00:00:00Z, stop: 2022-03-03T23:59:59Z) |> filter(fn: (r) => r._measurement == "yourMeasurementName") |> stateDuration(fn: (r) => r._value == 1, column: "state", unit: 1m)

    from(bucket: "yourDatabaseName/autogen") |> range(start: 2022-03-03T00:00:00Z, stop: 2022-03-03T23:59:59Z) |> filter(fn: (r) => r._measurement == "yourMeasurementName") |> stateDuration(fn: (r) => r._value == 2, column: "state", unit: 1m)

Again it's still not possible to do it in InfluxQL yet though the community has been waiting for this for a while. See more details here.

Munin
  • 1,576
  • 2
  • 19