Ok, I got this solution now, which is rather elegant compared to the queries I tried initially.
FYI this is why, the *nested select* statement did *not* work: see spoiler
the nested select statement mentioned in post#19 did not solve the problem because the WHERE clause of the “outer” select statement will again search within the timestamps of the original DB entries, and not within the calculated / forwardfilled timestamps of the inner select statement
The idea of #post19 was still valid but the execution needed to be a bit different:
Idea:
- in a first step we want to utilize a bigger timewindow with “forwardfill” to solve the two issues:
- issues with NaNs / “temporal gaps” in our data that cannot be forwardfilled within the user $timeFilter because they happen in the beginning of the user-timewindow → NaN
- and the bigger issue that almost none of our queries work at all in case there is no dp within the user $timeFilter window
- and then filter down to the original $timeFilter window which was selected by user. here we use transformations
Execution:
We only need one query now (Query name: “raw”):
SELECT last("value")
FROM "autogen"."Wohnzimmer_Heiz_Status_Stellgroesse_alle_3"
WHERE time > $__from * 1000000 - 7 * 24 * 60 *60 * 1000 * 1000000 AND time < $__to * 1000000
GROUP BY time($interval + 347s) fill(previous)
- we make the “normal” query but we push the time-window a few days ahead of the start of the user-timewindow by utilizing internal variable $__from and $__to (only changing the start window so we can ffill. the shift duration depends on the data. for me I can be sure that 7d is enough to find at least one datapoint even if there are no dp within the user-timewindow)
- the *1000000 is to convert from Grafana (unix ms) to InfluxDB (unix ns). However unix ms might work just fine as well (as I believe Influx can handle both).
About the ‘+347s’ I discuss below in “Optimization”
Transformations:
Now we use transformations to filter the data down to the actual user-timewindow. I want to use the between filter of the “Filter data by values”-transformation for this. However the “between” match is only available on data columns, not for the time index. Therefore we create a helper column as suggested here: post
See concrete implementation below (spoiler)
- Create unix_ms helper column: using transformation “Add field from calculation”
- Filter down to User-Timewindow: using transformation “Filter data by values” with the “between” match
- Hide all columns but the query column: using transformation “Filter fields by name”
- Calculate Mean value: using transformation “Reduce”, Mode “Series to rows” → “mean” calculation
- Multiply by 100 to have a nice percentage value shown in gauge: using transformation “Add field from calculation” → replace all fields
Screenshot of Transformations:
Result:
Gauge shows nicely 100% even though there is not a single dp within the user-timewindow (October 12th) - yay 
Optimization (GROUP BY time interval):
This is just to explain why I added the +347s in the query statement “GROUP BY time($interval + 347s)”
Since we query a fixed amount of 7days additionally to the user-selected timewindow, one can see why the $interval might not be perfect for this query out of the box. While it would not matter on big durations, e.g. 1y, it would dramatically increase the response rows for short durations e.g. 10 minutes (because on thos queries we naturally have a small $interval). Adjusting the $interval by a constant factor e.g. $interval * 10 would mitigate for short durations but would decrease the accuracy for big durations, e.g. 1y → constant factor does not scale well!
Solution for interval: regardless of the user-selected timewindow the $interval seems to have a somewhat fixed ratio in relation to the duration: it is roughly 0.0006 times the user-selected timewindow (I checked this empirically without reading the documentation about $interval). Thus, for enlarging the timewindow by 7d I add a constant summand of 7d * 0.0006 = ~347s which seems to work well for big and small durations.