How to properly Aggregate data to a graph

[grafjace]

Hi,

How can I set up my graph to take the already created kWh daily power consumption and show that value day over day for the month. So each day the Daily Power Consumtion single stat value gets aggregated to the monthly graph.

I have tried sum() tried few different things and not able to get the correct result.

Below is the screenshot of the graph settings.

To do something like this dashboard.

https://fablab.dyn.nerd2nerd.org/grafana/d/000000001/fablab-power?refresh=1m&orgId=1

That has the 30day power consumption.

I copied his dashboard and set my values, and not getting the desired result. I see he is querying the sum() of the value and grouping by day for a month time range. When I do that, I get this result.

My Y Axis is in MegaWhatts, and my Costs are $12,000. Which is not right.

[danielgonzalez]

hi again,

for day-to-day energy:

SELECT mean(“value”)*24 FROM “jace.home.power” WHERE (“entity_id” =
‘incoming_meter.kw_demand’ and $timeFilter GROUP BY time(1d)

for the cost:

SELECT mean(“value”)*24*0.11 FROM “jace.home.power” WHERE (“entity_id” =
‘incoming_meter.kw_demand’ and $timeFilter GROUP BY time(1d)

I would also recommend doing two graphics, since I do not think you can mix well energy and cost in bar graphs

[grafjace]

Thank you,

All makes sense. Wanted to ask one other thing.

When I hover over the graph data series, I get a date and time of 1700. in UTC time the database starts from 00:00 for a 12am PST new day start, but when the graph is loaded and the time zone is set to “browser local time” The new day starts at 1700 in PST. How do I offset the time to represent the correct new day start time?

In the below image I would expect the Date and the time to be 00:00 or a 12am PST new day start.

[danielgonzalez]

Sorry about utc and time zones, I am confusing with this

[shib4u]

@danielgonzalez Can you please help with similar queries for a graphite backend? Thanks in advance

[danielgonzalez]

Hello, I would like it, but I have never used graphite, maybe read a bit and if it seems easy, I’ll tell you