Displaying energy data over a year in month steps

Grafana Dashboards
, ,
1

I have an energy meter sensor that saves data to my MySQL DB every 30seconds.

Now I like to sum up the energy for each month and display it over the current year, every month it’s own bar like:

image
image1159×929 54.2 KB

But the DB has only data for Jun 2022 and July 2022 so two months but it shows me 3 months…

Can someone help me to fix this?

2

Hi @gekoch ,

Can you please do the following;

  1. Click on the “GenerateSQL” as showing in your provided screenshot.
  2. You will see that it will generate a similar look alike SQL query.
  3. Copy that query and login to your MySQL server instance.
  4. Select your database
  5. Paste the query there and see if the output also gives that one month extra or not ?
3

thx
yes it shows the same output:

image

4

So it looks like that the SQL query is not correct and that is why it does not the expected output you required (nothing to do with Grafana here).

The correct way will be 1st to fix your sql query to show results you require and then once that part done, can be used in the Grafana and use it in there.

You can do that directly via MySQL console (if you are familiar with) or inside Grafana and best way to do that is to goto “Explore” → select the correct “data source” and then define your query.

Hopefully it helps.

5

hmm ok thx.
That’s the problem that I can’t solve this for me complex MySQL query and I even don’t know if it is possible since not every month has the same amount of days etc.
So has nobody done that before or probably solved it differently by using as script that makes a new table with all the calculations already done separately and not via SQL querry

6

is your data dateime in local time zone or UTC? anyways, grafana uses UTC so some data towards end of July must be spilling into august.

1 Like
7

thx and yes my db is not on UTC it is on the timezone “Europe/Berlin”. But I set the settings “Session Timezone” to Europe/Berlin in the DB settings of Grafana so this shouldn’t be the problem or am I wrong?

8

hmm interesting if I use this query:

SELECT
  $__timeGroupAlias(time, 30d),
  sum(value)/120/1000 AS "server power consumption"
FROM shellyserverpm_power_0371
GROUP BY 1

with the Intervall of 30d it only shows 2 bars as expected. However when I set this to 31d I do get the same bars (3 of them) as with the 1M Intervall. So is 1M = 31d???
What about months with just 30days like June or 28 like February???

9

how about

__timeGroup(time,'1month')

or whatever it is for month?

10

for month it is ‘1M’ but no same graph…

11 
SELECT
  DATE_FORMAT(time, '%Y-%m') AS MONTH,
  sum(value) AS "server power consumption"
FROM shellyserverpm_power_0371
GROUP BY DATE_FORMAT(time, '%Y-%m')

image
image1537×441 21.3 KB

2 Likes
12

WOW thx you soooo much!
I feel so stupid not thinking about this solution. Thx for the help!!!

1 Like
14

Sometimes the solution might be outside grafana in the functions of the data source itself. Not stupidity just sometimes we get lost in grafana itself

15

Sum(value) is energy kwh or power kw?

Im trying to do the same. From an energy counter i am sending data to sqldb and i want to generate bar graph in grafana for daily, monthly energy consumption.