Struggling with bar chart for monthly consumption

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.


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.

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?

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

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

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:

1 Like

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


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?

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

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

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