Aggregate/Group Data in Grafana with SQL

InfluxDB 3
, , ,
1

Hi,

I am using Influxdb3 to Store data from my Data logger.

Writing to it with the following line Protocol spine,point=a_voltage,unit=V value=230.5 171234567890123456 works fine.

Currently I write multiple points every 250ms but the query in Grafana “sql” gets more heave over time.

Using following Query:

SELECT time AS "Time", value AS "A Phase Voltage"

FROM spine

WHERE point = 'a_voltage' AND time >= $__timeFrom() AND time <= $__timeTo()

Returns me every every stored point (250ms) which is fine for a short time period but Displaying like 24h gets difficult.

How can I use a custom variable or the Global Grafana $__interval one to Group/Aggregate Data to query more efficiently?

Hard-coding kinda works, but defeats the purpose of having such good resolution. And just using the variable instead of 1s does not work.

SELECT
  DATE_BIN(INTERVAL '1s', time, '1970-01-01T00:00:00Z') AS "Time",
  AVG(value) AS "A Phase Voltage 1s Interval"
FROM spine
WHERE point = 'a_voltage' AND time >= $__timeFrom() AND time <= $__timeTo()
GROUP BY 1

Thanks in advanced for the help!

2

Two thoughts:

Your approach here should work, and it’s curious that you say using a variable does not work. You can define and use multiple variables in Grafana, so you’d just need a second variable where you define and set your date-binned interval. If you create a variable called resolution, give it the possible values of 250ms, 1s, 10s, 1m, 5m, you can then modify your query to:

SELECT
DATE_BIN(INTERVAL '${resolution:raw}', time, '1970-01-01T00:00:00Z') AS "Time",
AVG(value) AS "A Phase Voltage 1s Interval"
FROM spine
WHERE point = 'a_voltage' AND time >= $__timeFrom() AND time <= $__timeTo()
GROUP BY 1

You also don’t need to do this, though. The InfluxDB connector for Grafana comes with a number of built-in macros, and Grafana auto-determines a reasonable $__interval macro based on your selected time range, which you can use in the $__dateBin function without needing any extra variables.

If you were to use that, you could just use:

SELECT
  $__dateBin(time) AS "Time",
  AVG(value) AS "A Phase Voltage"
FROM spine
WHERE point = 'a_voltage' AND $__timeFilter(time)
GROUP BY 1;

And you’d only need to use a custom variable if the default interval/date-binning wasn’t to your liking.