How about something like this?
Test data that I created:
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:
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)
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")
