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.
Can you click the pencil icon and paste the text of the query here (not a screenshot)?
SELECT
$__unixEpochGroupAlias(time,1h),
stddev(“E_CC01”) AS “E_CC01”
FROM “Energy_CC”
GROUP BY 1
ORDER BY 1
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
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?
try
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
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')
END
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