How to obtain time interval value reports from InfluxDB

Question

Using InfluxDB: Is there any way to build a time-bucketed report of a field value representing a state that persists over time? Ideally in InfluxQL query language

More specifically as an example: Say a measurement contains points that report changes in the light bulb state (On / Off). They could be 0s and 1s as in the example below, or any other value. For example:

time                   light
----                   -----
2022-03-18T00:00:00Z   1
2022-03-18T01:05:00Z   0
2022-03-18T01:55:00Z   0
2022-03-18T02:30:00Z   1
2022-03-18T04:06:00Z   0

The result should be a listing of intervals indicating if this light was on or off during each time interval (e.g. hours), or what percentage of that time it was on. For the given example, the result if grouping hourly should be:

Hour	Value
2022-03-18 00:00	1.00
2022-03-18 01:00	0.17
2022-03-18 02:00	0.50
2022-03-18 03:00	1.00
2022-03-18 04:00	0.10

Note that:

• for 1am bucket, even if the light starts and ends in On state, it was On for only 10 over 60 minutes, so the value is low (10/60)
• and more importantly the bucket from 3am to 4am has value "1" as the light was On since the last period, even if there was no change in this time period. This rules out usage of simple aggregation (e.g. MEAN) over a GROUP BY TIME(), as there would not be any way to know if an empty/missing bucket corresponds to an On or Off state as it only depends on the last reported value before that time bucket.

Is there a way to implement it in pure InfluxQL, without retrieving potentially big data sets (points) and iterating through them in a client?

Answer 1

I consider that raw data could be obtained by query:

SELECT "light" FROM "test3" WHERE $timeFilter

Where "test3" is your measurement name and $timeFilter is from... to... time period. In this case we need to use a subquery which will fill our data, let's consider grouping (resolution) time as 1s:

SELECT last("light") as "filled_light" FROM "test3" WHERE $timeFilter GROUP BY time(1s) fill(previous)

This query gives us 1/0 value every 1s. We will use it as a subquery.

NOTE: You should be informed that this way does not consider if beginning of data period within $timeFilter has been started with light on or off. This way will not provide any data before hour with any value within $timeFilter.

In next step you should use integral() function on data you got from subquery, like this:

SELECT integral("filled_light",1h) from (SELECT last("light") as "filled_light" FROM "test3" WHERE $timeFilter GROUP BY time(1s) fill(previous)) group by time(1h)

This is how it looks on charts:

And how Result data looks in a table:

This is not a perfect way of getting it to work but I hope it resolves your problem.