1

I have some measurements with boolean type values and want to calculate for how long was the signal in true state during a certain period of time.

For example:

datetime state
01.01.2021 01:00 true
01.01.2021 04:00 true
01.01.2021 05:30 false
02.01.2021 23:00 true
03.01.2021 01:30 false
05.01.2021 06:00 true

should get transformed to:

datetime duration(1h)
01.01.2021 4.5
02.01.2021 1
03.01.2021 1.5
04.01.2021 0
05.01.2021 18

I know that influx has the ELAPSED function that returns the time between subsequent records, but that doesn't seem to get me anywhere. I've tried it with flux as well but also hit a wall there (that was only for academic purposes, I need to it with InfluxQL).

I've found this, but the solutions there is to either use the INTEGRAL (that requires your values to be 0 or 1 instead of boolean and even them someone didn't get the right values) and switching to Timescale DB.

It's absolutely mindblowing to me that Influx still doesn't support this, it seems like on of the most often use-cases for the kind of data that influx is used for.

If anyone has any info or ideas that I might have missed, I'd be very grateful.

dskalec
  • 316
  • 2
  • 10
  • Did you finally find something about that? I'm looking the same. – Thibaut Sep 05 '21 at 10:49
  • @Thibaut I've patched up something using a subquery, but in order to do it you need to be able to use 1's and 0's instead of booleans. If that works for you, you could try something like this: `SELECT INTEGRAL(value, 1h) as "integral_1h" FROM (SELECT MEAN(value) AS "value" FROM your_series_name WHERE (time >= '2021-01-01T00:00:00+00:00' AND time < '2021-01-06T00:00:00+00:00') GROUP BY time(1s) fill(previous)) WHERE (time >= '2021-01-01T00:00:00+00:00' AND time < '2021-01-06T00:00:00+00:00') GROUP BY time(1d) fill(null) ORDER BY time ASC;` – dskalec Sep 06 '21 at 08:09

1 Answers1

1

Managed to get something working, but in order to do so, you need to be able to use 1's and 0's instead of booleans so you can use the INTEGRAL function in combination with a subquery.

SELECT INTEGRAL(value, 1h) as "integral_1h" FROM 
     (SELECT MEAN(value) AS 
     "value" FROM your_series_name  WHERE (time >= '2021-01- 
      01T00:00:00+00:00' 
      AND time < '2021-01-06T00:00:00+00:00') GROUP BY time(1s) 
      fill(previous))  
WHERE (time >= '2021-01-01T00:00:00+00:00' AND time < '2021-01- 
06T00:00:00+00:00') GROUP BY time(1d) fill(null) ORDER BY time ASC;
dskalec
  • 316
  • 2
  • 10