Is it possible to rewrite SAMPLE BY query as GROUP BY in QuestDB?

Community
,
1

I have the following SAMPLE BY query:

SELECT ts, min(temp), max(temp), avg(temp)
FROM temperature
SAMPLE BY 10s;

I’d like to rewrite it as a GROUP BY, so that QuestDB runs it multi-threaded. How can I do that? I’ve noticed date_trunc(unit, timestamp) in the docs, but the function doesn’t accept arguments like 10s.

2

You can rewrite the query in the following way:

SELECT (ts - ts%10000000)::timestamp as ts, min(temp), max(temp), avg(temp)
FROM temperature
ORDER BY ts;

Here, we truncate the timestamp to 10 seconds (ts - ts%10000000 - native QuestDB’s time resolution is in microseconds) and cast it from LONG back to TIMESTAMP column type. Notice that the GROUP BY clause is optional in QuestDB, so we omit it. The rest is as simple as ordering the result set time-wise with ORDER BY ts.