Monthly Bar Graph Postgresql DB

Grafana 9.1.6

I’m trying to get a bar graph that would display my energy consumption per month.
Any help would be greatly appreciated.

1 Like

Please share your query that was used to generate this data.

oh ya sorry because i’m new user, grafana limits my embedded attach file
here’s my query

Can you click the pencil icon and paste the text of the query here (not a screenshot)?

stddev(“E_CC01”) AS “E_CC01”
FROM “Energy_CC”

You are grouping by 1h = 1hour. You need to group it by 1 month

here’s the result if im change by 1 month

i want the result is in x axis display the month (Jan, Feb, Mar,…,Dec) or in graph they are show 12 bars it’s represent of month

Do you have 12 months worth of data in that table?

What if the data spans from June 2022 to June 2023 how should the months be displayed?

Try this instead

TO_CHAR(time, 'Mon') 

my database is running about 3 days

just changed the label of bar

what data type is the time column in Energy_CC table?
please post the DDL/Schema/Create table of Energy_CC


that is automatically generated from my scada software

that looks like nanoseconds or milliseconds?


select to_timestamp(time/1000);

now try

select TO_CHAR(to_timestamp(time), 'Mon'))

Plug that into your grafana query and see result.

As you can see your issue is not grafana but postgres query. I would post your query question in a postgres forum for better answers to find ways to fill missing months

1 Like

and this is when i’m applying in grafana

1 Like

thank you for your help, but what if my data is collected let’s say 2 years, will it later show 24 bars representing of 2 years?

test that situation by populating another table with bogus long term data and see what happens. another way of filling gap months but there are better ways

You can do the same fake data for 2 year span and test in grafana

select CASE 
      WHEN TO_CHAR(to_timestamp(Energy_CC.time), 'Mon') is null  THEN _anos._year
      ELSE TO_CHAR(to_timestamp(Energy_CC.time), 'Mon')
from (select 'Jan' as _year union select 'Feb' union Select 'March') _anos
left join (select 1670297070 as time) Energy_CC

 on TO_CHAR(to_timestamp(Energy_CC.time), 'Mon') = _anos._year