Struggeling with bar chart for monthly consumption

[ptn]

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.

[yosiasz]

@ptn

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:

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.

[ptn]

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?

[yosiasz]

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

[codlord]

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).

[ptn]

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: