Grafana: Improve SQL template to use DATE_BIN() (CrateDB 5.7+)

[coderabbitai]

Summary

In the Grafana end-to-end example (application/grafana/example-weather.py), the SQL query template currently uses $__timeGroupAlias("timestamp", $__interval) together with MEAN() for time-series aggregation.

It has been recommended to adopt the modern DATE_BIN() function (available since CrateDB 5.7) as the preferred SQL template for Grafana queries, since CrateDB 5.7 is already a reasonably old baseline.

Using the correct querying scheme and template is critical — as noted by @hammerhead — to avoid overloading the cluster or setting the wire and the browser on fire when processing large datasets. This is important knowledge that is not always applied correctly by users and customers.

Background

• @matriv flagged the DATE_BIN() function (recommended by @seut) as the modern replacement in this comment.
• The original discussion about the querying scheme can be found here.
• @zolbatar has volunteered to look into the details of the new DATE_BIN() variant.

Tasks

• Validate the DATE_BIN() SQL template for Grafana with CrateDB (coordinated with @zolbatar)
• Update the SQL query in application/grafana/example-weather.py to use the modern DATE_BIN() approach
• Add the legacy $__timeGroupAlias SQL template to the documentation as a reference for users on older CrateDB versions

References

• PR: Grafana: Basic end-to-end example / integration test #1442
• Comment: Grafana: Basic end-to-end example / integration test #1442 (comment)
• Requested by: @amotl

[amotl]

Thank you. Let me highlight the SQL expression hereby.

cratedb-examples/application/grafana/example-weather.py

Lines 107 to 114 in f93c1cf

	SELECT
	$__timeGroupAlias("timestamp", $__interval),
	"location",
	MEAN("{column_name}") AS "{column_name}"
	FROM "example"."weather_data"
	WHERE $__timeFilter("timestamp")
	GROUP BY "time", "location"
	ORDER BY "time"