Plotting only the first 24 hours of a selected time range for a dashboard

Grafana Dashboards
,
1

  • What Grafana version and what operating system are you using?
    Grafana v11.1.0 locally hosted on Windows 10

  • What are you trying to achieve?

I have hourly time series data that I will primarily be looking at through monthly time frames. I’m creating a panel which should summarize my hourly data by producing 24 hours representing the average day in the selected month. i.e. hour 1 is an average of every hour 1 from January 1 - January 31.

I’m able to summarize the data grouped appropriately, but I am not able to display it properly:

image
image1427×342 16.3 KB

The plot displays the full selected month of data, and the 24 hours of graphed summarized data are too small to be useful. How can I edit the panel to display only the 24 hours of summarized data, rather than plotting the whole selected time range? I haven’t been able to figure this out with the query options, but that might be where my solution is.

2

What is your data source and what is the query that you wrote to achieve the graph that you showed?

Can you also share what you have written in the Query options?
image

3

Sure!
I’m accessing a SQLite3 datasource.
Here’s my query and graph output:

image.png1332×759 47.7 KB

And here are my query options, which are all auto-populated at the moment.

4

Hi @catherinewhitten

I think you can accomplish this by adjusting your query. Since you only want to display 24 hours of summarized data (where each hour represents the average hour for the given month), I believe you need to use an EXTRACT function, GROUP BY hour, and a Bar Gauge where each bar represents 24 hours of the day.

Here is an example I created for Average Electricity Demand per Hour of the Day for region “Florida” spanning the month of July (“Previous Month” in time selector).

image
image1901×440 64.4 KB

SELECT
  EXTRACT(HOUR FROM "time") AS hour,
  "region",
  "time",
  "type",
  "value"
FROM "ElectricPowerOperations"
WHERE
  $__timeRange(time)
  AND
  "value" IS NOT NULL
  AND
  "region" = 'Florida'
  AND "type" = 'Demand'
GROUP BY hour, "region", "time", "type", "value"
ORDER BY hour;