QuestDB - Declaring variables or performing math in SAMPLE BY function

Viewed 174

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:

  1. declare a variable via DECLARE @sampler -- failed
  2. perform a WITH statement before the query via WITH sampler as (SELECT concat($__interval_ms / 1000, 'T') and then reference sampler at the end via SAMPLE BY sampler -- failed
  3. perform the math in a Grafana variable (via their standard dashboard variable GUI) -- failed
  4. embedding a SELECT statement in the SAMPLE BY function -- 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?

1 Answers

QuestDB as of 6.0.9 cannot parse non-constant SAMPLE BY period, is a way it can do SAMPLE BY 100s but cannot do arithmetic expression like SAMPLE BY 1000/10s.

Related