Hi, I am trying to migrate from Influx 1.x to GrepTime for our grafana dashboards. I've installed the greptime plugin but I'm having trouble translating the queries we use. Our metrics is collected are collected at various interval (1s, 5s, 1m) so we typically have a influxql like this: InfluxQL

SELECT mean("one") FROM "load" WHERE ("nvr_id" =~ /^$device$/) AND $timeFilter GROUP BY time($__interval) fill(null)

I think the equivalent SQL would be something like:

SSELECT
  DATE_TRUNC('minute', time) AS time_bucket,
  AVG("one") AS mean_one
FROM load
WHERE nvr_id = 'foo'
GROUP BY DATE_TRUNC('minute', time)
ORDER BY time_bucket

But I'm not sure if the greptime query builder supports something like this? I can't manage to "bucket" time slices. Similarly we also report changes: InfluxQL

SELECT non_negative_derivative(mean("value"), 1s) FROM "total_frames" WHERE ("nvr_id" =~ /^$device$/) AND $timeFilter GROUP BY time($__interval) fill(null)

I know the equivalent sql for this might be a little complex - but is something like this possible with the builder? Only asking because I thought our use case was pretty vanilla, so I don't know if I'm "holding it wrong"

I think you can use range query:

SELECT

time,

avg(one) RANGE '$__interval' FILL NULL

FROM load

WHERE

nvr_id =~ '$device'

AND $__timeFilter(time)

ALIGN '$__interval'

@dennis zhuang ok this worked perfectly.

That’s cool! Unfortunately, range query is a GreptimeDB-specific feature, so the MySQL datasource plugin query builder does not support it at all. I think we can support it in the GreptimeDB datasource. Would you mind creating an issue for this? The repository is: https://github.com/GreptimeTeam/greptimedb-grafana-datasource

Done https://github.com/GreptimeTeam/greptimedb-grafana-datasource/issues/38