Showing hourly average (histogramm) in grafana

Question

Given a timeseries of (electricity) marketdata with datapoints every hour, I want to show a Bar Graph with all time / time frame averages for every hour of the data, so that an analyst can easily compare actual prices to all time averages (which hour of the day is most/least expensive).

We have cratedb as backend, which is used in grafana just like a postgres source.

SELECT
  extract(HOUR from start_timestamp) as "time",
  avg(marketprice) as value
FROM doc.el_marketprices
GROUP BY 1
ORDER BY 1

So my data basically looks like this

time    value
23.00   23.19
22.00   25.38
21.00   29.93
20.00   31.45
19.00   34.19
18.00   41.59
17.00   39.38
16.00   35.07
15.00   30.61
14.00   26.14
13.00   25.20
12.00   24.91
11.00   26.98
10.00   28.02
9.00    28.73
8.00    29.57
7.00    31.46
6.00    30.50
5.00    27.75
4.00    20.88
3.00    19.07
2.00    18.07
1.00    19.43
0       21.91

After hours of fiddling around with Bar Graphs, Histogramm Mode, Heatmap Panel und much more, I am just not able to draw a simple Hours-of-the day histogramm with this in Grafana. I would very much appreciate any advice on how to use any panel to get this accomplished.

Answer 1

1. your query doesn't return correct time series data for the Grafana - time field is not valid timestamp, so don't extract only hour, but provide full start_timestamp (I hope it is timestamp data type and value is in UTC)
2. add WHERE time condition - use Grafana's macro __timeFilter
3. use Grafana's macro $__timeGroupAlias for hourly groupping

SELECT
  $__timeGroupAlias(start_timestamp,1h,0),
  avg(marketprice) as value
FROM doc.el_marketprices
WHERE $__timeFilter(start_timestamp)
GROUP BY 1
ORDER BY 1

This will give you data for historic graph with hourly avg values.

Required histogram may be a tricky, but you can try to create metric, which will have extracted hour, e.g.

SELECT
  $__timeGroupAlias(start_timestamp,1h,0),
  extract(HOUR from start_timestamp) as "metric",
  avg(marketprice) as value
FROM doc.el_marketprices
WHERE $__timeFilter(start_timestamp)
GROUP BY 1
ORDER BY 1

And then visualize it as histogram. Remember that Grafana is designated for time series data, so you need proper timestamp (not only extracted hours, eventually you can fake it) otherwise you will have hard time to visualize non time series data in Grafana. This 2nd query may not work properly, but it gives you at least idea.