How to filter and display data in Bar Chart by hour range

Grafana InfluxDB
1

Hello friends,
I’m using Grafana v10.3.3 on Windows and getting data from an InfluxDB.

My database stores an application login history data and I want to show it in grafana classified in three groups by hour range. 00:00 - 8:30am, 8:30am - 11:30am and 11:30am - 2:00pm) something like this:

image
image1114×675 28.3 KB

I used this below query in the screenshot above
SELECT count(“Type”) FROM “ICAU” WHERE (“active”::tag = ‘True’) AND time >= ${__from}ms and time < ${__to}ms-15h30m GROUP BY time(1d)
and I used three queries in the bart chart graph with different time range to show what I want.

Issue: The query above works only with one day range selected, if I choose a range date for more than one day it does not work because the two vars ${__from} and ${__to} takes the two days range to do the calculation and then the query shows the sum of the values of the two days in that hour range. Example below

image
image2149×1079 152 KB

I have tried:

  1. Transform Data → Filter data by values: This I can’t figure out how to set the range since it does note have the option.
  2. Do a subquery to get the data by date and filtered by hour range but grafana throws error

What I want to achieve is to show the data grouped by hour range when any date range is selected.

Any ideas please?

2

Hi @joelhdo and welcome to the Grafana forum.

So as you correctly noted, the queries you wrote do not work for a range longer than one day. I am not even sure this is possible using InfluxQL.

Have you considered using Flux or SQL instead of InfluxQL? In Flux and SQL, I created queries that do an aggregation by the hour of the day (i.e. 24 bars representing 24 hours). Maybe you could modify these queries to reflect the 3 bin sizes that you specified (00:00 - 8:30am, 8:30am - 11:30am and 11:30am - 2:00pm)?

Here’s a blog post comparing the 3 languages. Here are the two bar charts that “bin by the hour of the day” and will change based on the Time Picker (7 days, 30 days, 6 months, 1 year, etc.)

Flux

SQL

1 Like
3

Hey @grant2

I appreciate your answer. I followed your suggestion to try with Flux, I did not get it done not because is not possible, it was because I’m not familiar with Flux, this is what I got so far:

image
image2496×1412 279 KB

Using this:

*import "date"*
*import "strings"*
*from(bucket:"intellichief")*
*    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)*
*    |> filter(fn: (r) => r["_measurement"] == "ICAU")*
*    |> filter(fn: (r) => r["_field"] == "Type")*
*    |> filter(fn: (r) => date.hour(t: r["_time"]) >= 0 and date.hour(t: r["_time"]) <= 8)*
*    |> aggregateWindow(every: 1d, fn:count, createEmpty: false)*

Workaround: However I did find a workaround and I will see the behavior in the next couple of days. I owned the data base so I extracted the hour and minute value, concatenated them in a new column in the measurement and then filter the values between the range I’m expecting to graph in my query. I’ll see how it works.

Thank you so much!