How to threshold a time-series with InfluxDB 1.8?

Grafana InfluxDB
, ,
1

What Grafana version and what operating system are you using?

Grafana 9.2
Ubuntu 20.04
InfluxDB 1.8

What are you trying to achieve?

I have the following InfluxDB query (using InfluxDB 1.8):

SELECT "apower" AS power_state
FROM "pm_shelly"
WHERE time > now() - 30d
AND custom_id =~ /^shelly_122$/
tz('Europe/Zurich')

This yields a time-series like so:

image
image3204×558 43.2 KB

This is the power usage vs. time of a device.

I would like to determine the total amount of hours that the device was turned on in the last 30 days.

I found this forum post. It suggests converting the original time-series to a series consisting of 0 and 1 (where 1 is turned on; he calls the time-series generator_usage) and then use the integral function to obtain the total seconds with value 1. This seems like a reasonable solution.

However, I am unable to figure out how to obtain a time-series with 0 and 1 from my original series. I have tried this:

SELECT 
  apower, 
  (apower >= 10)::int AS power_state 
FROM "pm_shelly" 
WHERE time > now() - 30d 
AND custom_id =~ /^shelly_122$/ 
tz('Europe/Zurich')

where 10 would be the threshold in Watts, above which I consider the device as turned on. But this does not work with InfluxDB 1.8 and I have not been able to find good solution. (It was suggested by ChatGPT.)

I would greatly appreciate any input on how to achieve this!

2

Naive idea - count datapoints with nonzero value (device was on) and zero values (device was off). You know you collection frequency so based on that and count you can calculate on/off durations.

SELECT 
  COUNT(apower) AS on
FROM "pm_shelly" 
WHERE
  apower>0
...

SELECT 
  COUNT(apower) AS off
FROM "pm_shelly" 
WHERE 
  apower=0
...

I can imagine you can do everything with one query/subquery.