How to display time series data as a bar chart in Grafana?

Grafana Bar Chart Panel
, ,
1

I’m trying to display a timeseries data as a bar chart but Grafana complains about

Bar charts requires a string field

This is my query:

SELECT COUNT(footage_type) as _count_, BIN(time,24h) AS DAY FROM "footage"."footage" WHERE footage_type='VIDEO' group by BIN(time,24h) order by DAY

This is how my data looks in table form:

enter image description here

In timeseries form
enter image description here

But this happens in bar chart mode:
enter image description here

Likewise, the timestamps disappear in histogram mode:
enter image description here

What am I doing wrong ?

2

Welcome to the Grafana forum.

In the Time series graph that you showed above, why not just change to bars? It sounds like that’s all you want (bars instead of lines).

image
image2388×615 71.7 KB

1 Like
3

Thanks ! This did the job.

4

Hi guys,

I think i got this one figured out.
Bargauge panel expects the data for different bars as grouped values.
Like when you have a query with multiple fields, the data is grouped by field and there is one bar with the latest value for each field.

So this means the data just needs some formatting.
I use fluxquery to collect my data, it automatically returns multiple tables for grouped columns.
(By the way, using influxdb’s explorer you can see how columns are grouped)
When using a DB like Microsoft SQL, I think it is only possible to create a query per bar you need with a different alias for the barname.

The columns needed for Bargauge to work are
-A timestamp for the last value (don’t as me why, barnames get weird labels otherwise)
-A value
-A column for the different bars that the data is grouped by.

My example query for daily total of an always increasing value (with rollover → increase function) the reformatting of the date column is optional, this can just be the date including time 00:00:00.

from(bucket: "EnergyMonitor")
  |> range(start: -7d)
  |> filter(fn: (r) => r["_field"] == "EM5_E")
  |> increase(columns: ["_value"])
  |> aggregateWindow(every: 1d, fn: last, timeSrc: "_start")
  |> fill(column: "_value", value: 0.0)
  |> difference(nonNegative: false, columns: ["_value"])
  |> map(fn: (r) => ({ r with _date: strings.substring(v: string(v: r._time) , start: 0, end: 10)}))
  |> keep(columns: ["_time","_value","_date"])
  |> group(columns: ["_date"], mode:"by")

Hope this was helpful as my first contribution.

5

Hi guys,

I think i got this one figured out.
Bargauge panel expects the data for different bars as grouped values.
Like when you have a query with multiple fields, the data is grouped by field and there is one bar with the latest value for each field.

So this means the data just needs some formatting.
I use fluxquery to collect my data, it automatically returns multiple tables for grouped columns.
(By the way, using influxdb’s explorer you can see how columns are grouped)
When using a DB like Microsoft SQL, I think it is only possible to create a query per bar you need with a different alias for the barname.

The columns needed for Bargauge to work are
-A timestamp for the last value (don’t as me why, barnames get weird labels otherwise)
-A value
-A column for the different bars that the data is grouped by.

My example query for daily total of an always increasing value (with rollover → increase function) the reformatting of the date column is optional, this can just be the date including time 00:00:00.

from(bucket: "EnergyMonitor")
  |> range(start: -7d)
  |> filter(fn: (r) => r["_field"] == "EM5_E")
  |> increase(columns: ["_value"])
  |> aggregateWindow(every: 1d, fn: last, timeSrc: "_start")
  |> fill(column: "_value", value: 0.0)
  |> difference(nonNegative: false, columns: ["_value"])
  |> map(fn: (r) => ({ r with _date: strings.substring(v: string(v: r._time) , start: 0, end: 10)}))
  |> keep(columns: ["_time","_value","_date"])
  |> group(columns: ["_date"], mode:"by")

Hope this was helpful as my first contribution.