Struggling with bar chart for monthly consumption

Grafana Bar Chart Panel
1

Hello world,

I am running Grafana 9.2.0 on Ubuntu 18.04 LTS and mysql as database.
Visualizing data over time imported from my central heating system works fine.
Now I want to create an bar chart which shows me the kWh consumption per month.
The column time is of datetime type.
This is the SQL select which should be transferred:

mysql> SELECT year(time),month(time),sum(anfbren/100*25)/60 AS “kWh” FROM data group by year(time),month(time);
±-----------±------------±--------------+
| year(time) | month(time) | kWh |
±-----------±------------±--------------+
| 2020 | 10 | 999.13333333 |
…
| 2023 | 1 | 819.30000000 |
±-----------±------------±--------------+

I’ve created a bar chart and get the error, that Bar charts requires a string or time field. Next step was to concat year and month:

SELECT convert(concat_ws(‘’,year(time),month(time)),CHAR) as month,sum(anfbren/100*25)/60 AS “kWh” FROM data group by month;

±-------±--------------+
| monat | kWh |
±-------±--------------+
| 202010 | 999.13333333 |
…
| 20231 | 819.30000000 |
±-------±--------------+

Currently I don’t get the dots connected, how to match group by and time fields and / or generating a string.
Any help appreciated.

B.

2

As you can see in the Format drop down, you need to select the proper format.
If you do not have a proper time field then you cannot choose time series.

image
image1146×774 32.7 KB

3

My data source is a mysql database and no csv as in your screenshot.
So that doesn’t help unfortunately. But thanks for pointing that out.
Any other idea or different approach?

Auswahl_145
Auswahl_1451413×497 31.5 KB

4

The csv is just to emulate your data since we dont have access to your data. Its an easy quick and dirty way to get your data

Add a fake field and try

'Fake' as fake
5

I don’t know if it’s the “right” way but I do all my bar charts as time series like this and it works for me:

SELECT now() as time,
       bar_titles_field as metric,
       bar_value_field as value
FROM ...
WHERE ...
GROUP BY metric;

So metric is always whatever you want the bar titles to be, in your case your year/month or whatever. And value is the value of the bar (kWh in your case).

6

Okay… I’ll try codlords approach as well but yosiasz hint did it.
So to sum up, here is the SQL statement and the bar chart setting for others who’ll try this:

Auswahl_146
Auswahl_1461414×326 28.6 KB

1 Like
7

And if you have 2 devices, and want to group by device and month on the same chart:

ex:

Device1 Jan Device2 Jan Device1 Feb Device2 Feb, etc… ?

it shows, the 2 bars for each month (1 for each device), but how to put the right labels, and different color per device?

8

Hi @mvrk

Do you mean something like this? (the time labels show the last day of the month, but could probably be formatted as Jan-2024, Feb-2024, etc.)

image
image1607×645 81.4 KB

SQL query for above is:

SELECT date_Bin(INTERVAL '1 month', time, TIMESTAMP '1970-01-01 00:00:00Z') AS time, sum("value") AS sum_value, region
 FROM "ElectricPowerOperations" WHERE "type" IN ('Demand') AND "region" IN (${region}) AND $__timeRange(time) GROUP BY 1, region
 ORDER BY time ASC
9

thank you, i know now what i was missing, my query was ok but i forgot the partition by value on the Transform data.

1 Like