Limit query to current view

Grafana Time Series Panel
1
  • What Grafana version and what operating system are you using?

10.2.3 Enterprise on Linux arm64

  • What are you trying to achieve?

Limit the database query on a dashboard graph to the current time range on the dashboard, because Grafana appears to be running the query without restricting the time range, which is slow. If I select a large time range, I expect it to be slow, but with the default time range set to 24 hours, it is still trying to retrieve all the rows in the table, without restricting the time range.

Background: I’m looking to migrate a temperature monitoring system to Grafana. The database contains temperature observations at 1 minute intervals over a period of several years, since 2014. The current web UI I am using is a custom system I wrote from scratch in a mixture of javascript, html, css and PHP. I would like to migrate to Grafana for ease of future maintenance, and also ease of adding new features, since I am not a skilled programmer.

The database is in a MariaDB Aria table, with the MariaDB server running on the same machine as Grafana: a Raspberry Pi 4B. The time of each observation is stored in the database as a DATETIME.

  • How are you trying to achieve it?

I created a new dashboard using Grafana’s web UI, and created a visualisation, i.e. a graph, with it as well.

2

Welcome @andrum99

What does your query look like?

3 
SELECT dt, ambient2 FROM observation.temperature ORDER BY dt DESC;

I added the ORDER BY clause since Grafana was warning that it had hit the 1 million rows limit, so it would only have returned the first few years of data since 2014. dt is a DATETIME field containing the date and time that the temperature reading was taken, while ambient2 is the temperature reading from the ambient2 sensor.

I’m wondering if there is perhaps a pair of variables I’m missing that would limit the query to the date range selected in Grafana’s web UI?

4

Did some googling. It seems I need to use $__timeFilter(dt) in the query. If I do that, then the query seems to be limited to using the date range selected in Grafana’s web UI.