Display data as histogram collected by buckets for time

[mazzy89]

• What Grafana version and what operating system are you using?

Grafana 8.1.2 and InfluxDB as datasource

• What are you trying to achieve?

I’m trying to replicate this dashboard into Grafana.

I have already access to the values like daily_energy_vt and daily_energy_nt into Grafana.

• How are you trying to achieve it?

The values I’m trying to graph need to be grouped by time of the day. I’ve expected that would be easier but either I’m overthinking to the solution or the thing is not possible and more transformation is needed.

• What happened?

The values at the moment are simply graphed as an increasing curve. I have not found a way to group them by time of the day.

• What did you expect to happen?

• Can you copy/paste the configuration(s) that you are having problems with?

• Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

• Did you follow any online instructions? If so, what is the URL?

[grant2]

Can you share the queries that you have tried thus far and the results they have produced?

[mazzy89]

here it is.

[grant2]

I see you are using Time Series. In the upper right, what happens when you change to Histogram?

Please change and then post the same screenshot.

[mazzy89]

problem is that histogram in x axis does not have the time

[grant2]

OK, so obviously Histogram is not the right visualization tool here because you want aggregate (sum of the energy values) on an hourly basis, but still want to use the Grafana dropdown (6 hours, 1 week, etc.) to change the time scale, correct?

Am pretty sure this is possible, but will take some experimentation. In FluxQL, it’s much easier, but you are using InfluxQL, so click the pencil icon to view the query and then maybe try changing $timeFilter GROUP BY time(1h) fill(null)

[mazzy89]

I’ve tried but nothing. it did not work. I’m pretty sure that this is not directly possible in Grafana w/o before making a transformation on those data.

[grant2]

Like I said, you will probably have to write the query directly in the query editor (not using Grafana’s query builder) to get what you want. This post might be a good jumping off point.

[mazzy89]

I’ve also enabled fluxql so it should be now easier. I will play a bit and see if I can get to the solution and then I’ll post it back. I’m new to fluxql so no idea atm how to write that query.

[mazzy89]

Unfortunately I couldn’t use Flux. it’s very buggy and indeed I cannot use it properly. The issue is even tracked here but no much interest to fix Error: panic: runtime error: invalid memory address or nil pointer dereference · Issue #21649 · influxdata/influxdb · GitHub
So I have to use the InfluxQL.

I’m close to the solution.

I wrote a query like this

SELECT max("value") AS "max_value" FROM "homeassistant"."autogen"."kWh" WHERE time > :dashboardTime: AND time < :upperDashboardTime: AND "entity_id"='daily_energy_nt' GROUP BY time(1h) FILL(null)

and this give me the increment aggregated by hour.

If only I could do the diff I could get the value per hour.

Does Grafana has a way to calculate the diff between the two increments?

[grant2]

Maybe this solution? Calculate difference between two measurements - #7 by aleksas

[mazzy89]

I have found a way using Flux that does not take into account my issue

from(bucket: "homeassistant/autogen")
  |> range(start: v.timeRangeStart)
  |> filter(fn: (r) => r._measurement == "kWh" and r._field == "value")
  |> filter(fn: (r) => r.entity_id == "daily_energy_nt")
  |> aggregateWindow(every: 1h, fn:max)
  |> difference()

This calculate the difference between two subsequent values however i lose the first value.

[mazzy89]

from(bucket: "homeassistant/autogen")
  |> range(start: -1d)
  |> filter(fn: (r) => r._measurement == "kWh" and r._field == "value")
  |> filter(fn: (r) => r.entity_id == "daily_energy_nt")
  |> aggregateWindow(every: 1h, fn: spread, createEmpty: true)
  |> fill(usePrevious: true)

ok this solves pretty well my thing but the graph is pretty ugly

which could be the best way to span those bars?

[grant2]

Do you see the Bar Width slider tool in the options panel?

[mazzy89]

Bar chart is not for this kind of usage. Bar chart requires strings

[mazzy89]

solved. i’m using the old one Graph panel

the new one is pretty basic and does not support many things

[mazzy89]

actually after a while the width got back again to very small values so i can’t see the values. anyways seems an issue for a different topic.

[akseth]

In the same time series panel, you can choose the display to be in form of “bars”. Refer image below.

I hope this solves the issue.

[mazzy89]

thanks but the problem is already solved and the solution has been already marked.

Further I was already selecting Bars. check above my screenshoft.