0

Just new to InfluxDB.

I have a monitored IO signal that everytime it is changed then InfluxDB will record its timestamp. The data is like below:

time                   value
----                   -----
2020-03-19 06:02:50    0
2020-03-19 06:01:28    1.00
2020-03-19 03:25:58    0  
2020-03-19 03:22:38    1.00
2020-03-18 23:07:47    0
2020-03-18 23:06:47    1.00
2020-03-18 21:14:47    0
2020-03-18 21:12:45    1.00
2020-03-18 21:11:23    0
2020-03-18 21:09:41    1.00
2020-03-18 21:07:30    0
...

Now I want to get the elapsed time for value = 1 ONLY (which means the elapsed time that the switch is on)

Firstly I tried SELECT elapsed("value")/3600/1000000000 FROM "MyMeasurement" then I got a series like this:

time                   elapsed
----                   -----
2020-03-19 06:02:50    0.02
2020-03-19 06:01:28    2.59
2020-03-19 03:25:58    0.06
2020-03-19 03:22:38    4.25
2020-03-18 23:07:47    0.02
2020-03-18 23:06:47    1.87
2020-03-18 21:14:47    0.03
2020-03-18 21:12:45    0.02
2020-03-18 21:11:23    0.03
2020-03-18 21:09:41    0.04
2020-03-18 21:07:30    0.02
...

how can I narrow down the series to those I concern [0.02, 0.06, 0.02, 0.03, 0.03, 0.02 ...]? I have to do this in InfluxDB query command since I'm popping this onto Grafana panel. Can anyone help me on this please?

Sean Hsieh
  • 305
  • 2
  • 13

1 Answers1

1

You could use a nested query. Try something like this

SELECT elap FROM
      (SELECT difference("value") as diff, elapsed("value") as elap FROM "MyMeasurement")
WHERE diff > 0

If the values in "value" are always just 0 and 1, then the difference between consecutive values will either be 1 or -1. You can use this as a filter to get elapsed only for the records you want. (Not sure if you have to take 1 or -1, just check)

PS: I have the feeling I've already answered a similar question on StackOverflow but could not find it.

Nikolay Hüttenberend
  • 1,288
  • 8
  • 17