Aggregation issues

Grafana Configuration
1

I am new to Grafana, but seem to have the basics working with an Influxdb database and can display time series data. What I want to do seems very simple, but I cannot get it done. I may almost be there, but not quite. Here is the setup. I have a very simple one category data set (plus timestamp of course). Each value in the data is a number and it will vary from perhaps 5 to 60. The number is generated when an event occurs, so will happen at random times. I can display this as a time series bar graph and see all the little bars over whatever time period I choose based on the dashboard drop down menu.

Here is what I am trying to do and not getting there. I want to aggregate all the data for each day by summing the values and then displaying one month of data with the total for each day being one bar in the graph. I have been using sum() and then choosing group by interval. However, the interval period only goes up to one hour. This appears to aggregate things (from what I can tell), but clearly only aggregates by hour, not by day. I am totally at a loss as to how to get what I think should be a simple thing done. I have reviewed many posts on the community and scoured the internet, but so far not found anything that explains what I must be doing wrong. Hence, hoping the helpful people here can steer me in the right direction. Thanks in advance and sorry if this is such a simple thing.

2

Grafana has “magic” variable $__interval, which calculates aggregation period based on current dashboard time range automatically.

But you can define time groupping period explicitly. So if you need 1d, then:

SELECT sum("field") 
FROM measurement
WHERE $timeFilter 
GROUP BY time(1d)

You can also create own dashboard variable (Type: Interval) and define own aggregation periods there + custom auto value as well and then you can use it in GROUP BY time($mycustomaggregationvariable).

2 Likes
3

Thank you so much jangaraj. This has helped enormously and I am also now feeling super stupid because I realise that I can type “1d” into the group by time field. I thought I could only select from the values in the drop down list. How dumb of me! I had read about the custom variable and even set one up based on a tutorial, but could not figure out how to use it. You have explained that too. Thank you so much :smile: