Goodday!.
I am having a small problem that might be obvious but cant clear this one…
I have shift started at 0623 and ended at 1826 that would be about 12.03 Hours
The following returns 10.8 hours. If I skip the division to get hours and to it manually I get 38747 Seconds which equates to 10.76 Hours. So there is about 1 hour missing I am guessing it is getting lost somewhere. This querry will only run on 24 hours of data max.
Can anyone help me improve the code?
Thanks.
from(bucket: “Test Logger”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“device”] == “ESP8266”)
|> filter(fn: (r) => r[“SSID”] == “HCAp1”)
|> filter(fn: (r) =>r[“_field”] == “ShiftStatus”)
|> elapsed(unit: 1s)
|> map(fn: (r) => ({ r with elapsedFloat: float(v: r.elapsed)/3600.0 }))
|> filter(fn: (r) => r[“_value”] == 1)
|> aggregateWindow(every: 1d, fn: sum, column:“elapsedFloat”)
|> yield(name: “sum”)
You may have to use a second tool to find this problem.
What did this part of your query return on its own ? Does it match your expectations for number of data points? (Eg. Could use excel to count them) are there any “empty” seconds that don’t exist in the bucket at all?
from(bucket: “Test Logger”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“device”] == “ESP8266”)
|> filter(fn: (r) => r[“SSID”] == “HCAp1”)
|> filter(fn: (r) =>r[“_field”] == “ShiftStatus”)
|> elapsed(unit: 1s)
If you’re satisfied that part of the query is all valid and makes sense, then dig further into the other other functions one by one.
Hi and thanks for your response.
the query did show up something…
Data gets written about every 6 seconds… in some cases I counted 8 seconds but it only accounted for 7… (about every minute) in 12 hour this makes a lot and could induce errors… It’s impossible for me to check all the entries since there are about 14K. I am going to modify my software to update only on change of status, I am guessing this might make a diffference, less inputs and less approximations…
Right now, when I calculate value ON and OFF in 24 hours I get 18.17 hours while logically this should be 24…
I shall revert shortly!.
You might benefit from the fill function - may need some experimenting though where to place it in your query chain
