Monthly Bar Graph Postgresql DB

[andreapraja11]

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.

[grant2]

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

[andreapraja11]

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

[grant2]

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

[andreapraja11]

SELECT
$__unixEpochGroupAlias(time,1h),
stddev(“E_CC01”) AS “E_CC01”
FROM “Energy_CC”
GROUP BY 1
ORDER BY 1

[yosiasz]

@andreapraja11

$__unixEpochGroupAlias(time,1h)

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

[andreapraja11]

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

[yosiasz]

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

[andreapraja11]

@yosiasz

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

my database is running about 3 days

[andreapraja11]

@yosiasz

TO_CHAR(time, 'Mon')

just changed the label of bar

[yosiasz]

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

[andreapraja11]

that is automatically generated from my scada software

[yosiasz]

that looks like nanoseconds or milliseconds?

try

select to_timestamp(time/1000);

[andreapraja11]

[andreapraja11]

@yosiasz

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

[andreapraja11]

and this is when i’m applying in grafana

[andreapraja11]

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?

[andreapraja11]

Sorry yesterday I have exceeded the reply limit

here’ the results

where does the data come from?
i want to find the difference from the value of each month in E_CC01

[andreapraja11]

Let’s assume I already have data spanning 2 years

SELECT
  TO_CHAR(to_timestamp(date), 'Mon-YY'),
  stddev("E_CC")
FROM "Energy_CC"
GROUP BY 1
ORDER BY 1

how to sort from jan to dec

[andreapraja11]

yes, but I still don’t understand