Background:
I am using Telegraf, QuestDB, and Grafana to visualize high-frequency time-series data. In order to reduce the time Grafana takes to query long time ranges of data, I need leverage QuestDB's SAMPLE BY function.
Goal:
I need to make my QuestDB query dynamic based on a simple math equation leveraging Grafana's system variable $__interval_ms and an arbitrary numeric.
Expected:
In query below, I am attempting to leverage Grafana's system variables to make my query dynamic. I am expecting QuestDB to understand and perform the mathematical operation necessary to complete the SAMPLE BY function.
SELECT ts time, last(x), last(y), last(z)
FROM accelerometer
WHERE $__timeFilter(ts)
SAMPLE BY ($__interval_ms/1000)T
After Grafana processes this query, it is passed to QuestDB as...
SELECT ts time, last(x), last(y), last(z)
FROM accelerometer
WHERE ts BETWEEN '2021-10-12T00:00:00.000Z' AND '2021-10-12T01:00:00.000Z'
SAMPLE BY (30000/1000)T
NOTE: if I replace SAMPLE BY (300000/1000)T with SAMPLE BY 30T, the query performs as expected.
Result:
QuestDB does not recognize that I wish to perform a mathematical operation and fails.
Steps I've Taken:
I have tried to do the following as workarounds:
- declare a variable via
DECLARE @sampler-- failed - perform a
WITHstatement before the query viaWITH sampler as (SELECT concat($__interval_ms / 1000, 'T')and then referencesamplerat the end viaSAMPLE BY sampler-- failed - perform the math in a Grafana variable (via their standard dashboard variable GUI) -- failed
- embedding a
SELECTstatement in theSAMPLE BYfunction -- failed
So far I've had absolutely no luck.
How can I either store a variable in QuestDB (similar to DECLARE in Postgres) or perform mathematical operations in the SAMPLE by function? Or is there another way to tackle this problem that I am not thinking of?