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?