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
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.
Thanks for you answer! I appreciate your help.
Its probably because I’m quite new to Influxdb and Grafana.
But I got it working with the following:
SELECT
time_bucket AS "Time",
${aggregation_value}(value) AS "A Phase Voltage"
FROM (
SELECT
CASE
WHEN '${group_interval:raw}' = 'raw' THEN time
ELSE date_bin(INTERVAL '${group_interval:raw}', time)
END AS time_bucket,
value
FROM spine
WHERE
point = 'a_voltage'
AND time >= $__timeFrom()
AND time <= $__timeTo()
)
GROUP BY time_bucket
One caveat: ${aggregation_value} is interpolated as SQL, so limit that variable to fixed options such as AVG, MIN, and MAX, rather than allowing arbitrary values. Also, the raw branch groups by each timestamp, so it returns the original point density; use $__dateBin(time) when you want Grafana to choose bins from the panel’s time range.
