Torkel, Thanks for the response.
If I change the query to the following (I used fill(previous) rather than fill(0) as the previous state should continue until the next new measurement):
SELECT mean(“status”) FROM “station” WHERE $timeFilter GROUP BY time($interval), name_tag fill(previous)
Then I get the correct graph without the diagonal artefacts but only when viewing a relatively small time interval.
Unfortunately, aggregation works against me when I “zoom out” to view say a month’s worth of data. In that situation the time between my zone “on” and “off” events is less than $interval and aggregation returns an unhelpful average (i.e. neither “on” nor “off”). This value then gets repeated by the fill(previous) command until the next set of recorded measurements a few days later. You can see in the graph below that the green and yellow zones appear to be “on” for three consecutive days whereas in reality the zones are on for a few minutes every three days. Aggregation is blending out the “off” events.
I think this boils down to a conflict between the nature of a “binary sensor” (i.e. only two valid states) and the aggregation approach. Aggregation doesn’t really work for a binary sensor as the “mean” is not a valid proxy for that interval. Using min()/max()/sum() etc all have their own problems as they still effectively blend discrete states into one.
Hence, I am keen to see if my original query approach, which avoids aggregation, can be made to work. The graph in my original post can be replicated using the following query, test data and display options:
SELECT "status" FROM "station" WHERE $timeFilter GROUP BY "name_tag"
station,name_tag=Dicksonia status=1 1502969602361792700
station,name_tag=Dicksonia status=0 1502969662371432000
station,name_tag=Bamboo status=1 1502969714569562700
station,name_tag=Dicksonia status=1 1502969775969333000
station,name_tag=Dicksonia status=0 1502969837468813400
station,name_tag=Bamboo status=0 1502969895650165500
It does look as though the shading algorithm is incorrectly shading above the line rather than below in the diagonal section.
I am new to this so if I have got this completely wrong then apologies for taking up time.
