Count (in seconds) how long a value is '1'

Fluxlang
1

dear all,

I want to measuere how long we are using our smartphones during the day,
so eveytime the smartphone is active a ‘1’ is sent to the influx database, and when the phone is not active anymore, a value ‘0’ is sent., so i want the sum of all the times (in seconds) during a certain timeperiod (1 day) t that the value is ‘1’: for example
10:15:05 → 1
10:16:05 → 0
10:30:30 → 1
10:30:45 → 0

so the flux querry should return ‘75’ (60 + 15) .

can someone help me with this?
regards,
Thomas

2

Hi @Thomas_Decock

How about something like this?

Test data that I created:

image
image1834×790 109 KB

Add an elapsed() function to the query:

from(bucket: "junkbucket")
  |> range(start: 2023-02-11T11:35:04.493Z, stop: 2023-02-11T11:38:57.369Z)
  |> filter(fn: (r) => r["_measurement"] == "BinaryData")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["device"] == "smartphone")
  |> filter(fn: (r) => r["person"] == "Thomas")
  |> elapsed(unit: 1s)
  |> yield(name: "sum")

gives us an additional column called elapsed that shows the number of seconds from each consecutive record:

image
image1793×570 82.6 KB

Next, add a filter statement after the elapsed function to filter out only those whose _value = 0. This is because the “active” time (when the state 1 goes to state 0) is captured in those records where _value = 0. If you wanted to know the time when the phone is NOT active, you would set the filter where _value = 1.

  |> filter(fn: (r) => r["_value"] == 0)

image
image1790×355 46.6 KB

Lastly, sum up the values in the “elapsed” column:

from(bucket: "junkbucket")
  |> range(start: 2023-02-11T11:35:04.493Z, stop: 2023-02-11T11:38:57.369Z)
  |> filter(fn: (r) => r["_measurement"] == "BinaryData")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["device"] == "smartphone")
  |> filter(fn: (r) => r["person"] == "Thomas")
  |> elapsed(unit: 1s)
  |> filter(fn: (r) => r["_value"] == 0)
  |> sum(column:"elapsed")
  |> yield(name: "sum")

image
image1816×162 15.1 KB

1 Like
3

Thank you a lot for this!
This works great! This is what I wanted.

If I want to see for each day how much the smartphone was active I guess I have to use the:
'aggregateWindow" function?

let’s say I have the range set to ‘last week’, and i want to see for every day what the useage of the phone was, i will need to use this i think?
|> aggregateWindow(every: 1d , fn:last, createEmpty: false)

but this does not seems to work. I am not sure where to put this function.

4

Hi @Thomas_Decock

Remove the line:

  |> sum(column:"elapsed")

and replace with this:

  |> aggregateWindow(every: 1d, fn: sum, column:"elapsed")
5

thank you a lot for this!
I thought of it when i was in bed to do it this way! But great to hear I was thinking the correct way to do it.

I sitll have a follow up queston… (i allready tried several things but I am not able to do it…)
now i have for each day the total seconds a phone was used.
but if i want to convert this to ‘hours’…
i was thinking about using this function:

|> map(fn: (r) => ({_time: r._time, hours: r.elapsed/3600.0} ))

but the r.elapsed value is not an integer/float so it’s not possible to do this kind of operation with it…
i also tried using the int() or float() fuction for the r.elapsed… but this does not work

6

Hi @Thomas_Decock
I think this will work. There may be a more elegant way to just have one single column called elapsed which has the float value, but I created a second column called elapsedFloat and then divided by 3600.0 to get the decimal hours.

from(bucket: "junkbucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "BinaryData")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["device"] == "smartphone")
  |> filter(fn: (r) => r["person"] == "Thomas")
  |> elapsed(unit: 1s)
  |> map(fn: (r) => ({ r with elapsedFloat: float(v: r.elapsed)/3600.0 }))
  |> filter(fn: (r) => r["_value"] == 0)
  |> aggregateWindow(every: 1d, fn: sum, column:"elapsedFloat")
  |> yield(name: "sum")

image
image1527×160 22.5 KB

1 Like
7

Thanks a lot for all of your help!
I learned a lot! I sure like the possibilities of ‘Flux’ but it’s a lot to learn.

8

Hello, this exact solution is what I have also been searching for, however I am using influxdb cloud serverless, so querying with flux is not available, is there an SQL solution?

9

Wow!
Thanks for your educational help here.
I’ve learned a lot.
I have a question though.
When the state is 1 currently it seems that this query would not capture the active time. (the current chunk that did not go to 0 yet).
How can I add that to the result?
Many thanks!

10

Hi @Sepp and welcome to the InfluxDB forum.

It has been a while since I crafted that query, so I no longer have the test data lying around, but I am sure there is a way to capture the current state (up to the time that the query is run) when that value is “1”. What happens if you modify the aggregateWindow function to include timeSrc: "start" ?

|> aggregateWindow(every: 1d, fn: sum, timeSrc:"_start", column: "elapsedFloat")

More info here: aggregateWindow() function | Flux Documentation

12

Thanks for your answer. Unfortunately, the result does not match the expected number (from the stateDuration, which is correct from looking at the graph visualization) - the number is off by quite a bit - I’m expecting around 4.8 hours, but I get 3.85 hours

13

Do you wish to share your query and data, or are you certain that you have thoroughly worked thru everything?

15

Sorry I didn’t see your reply until now - I must not have enabled notifications.

I’ll see if I can pull together the example. In my particular example, I’m interested in how long a value has been above a threshold.

This thread (and others, where you have contributed too - thanks! :blush: ) helped, and I figured out something that works mostly (except if the threshold is still exceeded at the end of the time period, for which I think I’ve found the solution). In my case, I used a combination of aggregateWindow, filter, stateDuration, elapsed and map to achieve my result, which I think tracks with the expected values.

Thanks

1 Like