Daily power consumption profile graph

InfluxDB 2 Dashboards
,
1

Hi,

I am collecting the following data into influxdb from smartthings:

  • power: current value in kW
  • energy: cumulated value in kWh

With a very simple request I can display the power consumption over time in grafana:
SELECT mean("value") FROM "power" WHERE $timeFilter GROUP BY time($__interval), "deviceName" fill(previous)

What I am looking for now is to build a request that will profile my power consumption over a day, averaging each minute of the day over a period of time.
So that graph would only display 1 day from 0 to 24h and draw a line that would be my average consumption per (1|10|30?) minute(s) increments over the selected period.

This looks like a simple concept to me but I am not sure how to explain it, that’s maybe why I wasn’t able to find that answer yet :smiley:
Let me know what you think about that, any help is appreciated.
Thank you

2

Hi
I have:
SELECT non_negative_derivative(last("value"), 1d) FROM "device_119_usage" WHERE $timeFilter GROUP BY time(1d) fill(linear)
where device_119_usage is momentary power usage
Also time range I set to 1M.
So I get daily usage in kWh in separate bars for every day.

3

Thanks @xury but this is not what I am looking for, at least I don’t think so.
What I am looking is 1 graph over one 24h period that gives me my average consummation hour per hour for the selected period.
for example, for a selected period of last 7 days, I want that graph to show me 24 points on the x-axis:

  • average of power consumption between 0am to 1am over the last 7 days
  • average of power consumption between 1am to 2am over the last 7 days
  • average of power consumption between 2am to 3am over the last 7 days
    …
  • average of power consumption between 11pm to 0am over the last 7 days

Does that make more sense explained like that ?
Thanks for your help !

4

Still cannot figure this out. Is this even possible ?
My request should return me 24 lines:

  • average of power consumption between 0am to 1am between day A and day B
  • average of power consumption between 1am to 2am between day A and day B
  • average of power consumption between 2am to 3am between day A and day B
    …
  • average of power consumption between 11pm to 0am between day A and day B

the result is a line that gives me my average power consumption, hour per hour, between day A and day B

Any help is appreciated. I might be taking the wrong tool or direction with that

5

Hi @cben0ist

Is this of any help?

SELECT INTEGRAL(*,1h) FROM CONS WHERE TIME >= now() - 7d GROUP BY time(1h)
(CONS = my energy consumed metric.)

If you set Display -> Draw Mode to Points, it should give you something like this:

image
image1770×300 9.62 KB

Regards,

Bill

6

Hi @cben0ist,
Did you have success during the last year? I am trying to achieve the same and could not find any sources for it here or via Google.
Regards
Alex

7

No, but let me know if you find a way to do it please.

8

Maybe this could help. It is the visualization for my smartmeter with histograms per day and month

9

Hi @digitaldex,

I can’t test the visualization in Grafana right now, but had a look at the query:

|"query": "today = from(bucket: \"piMeter\") |> range(start: -31d) |> filter(fn: (r) => r._measurement == \"downsampled_energy\" and r._field == \"sum_Gesamt\") |> aggregateWindow(\n every: 24h,\n fn: sum,\n timeSrc: \"_start\",\n createEmpty: false\n) yesterday = from(bucket: \"piMeter\") |> range(start: -62d, stop: -31d) |> filter(fn: (r) => r._measurement == \"downsampled_energy\" and r._field == \"sum_Gesamt\") |> aggregateWindow(\n every: 24h,\n fn: sum,\n timeSrc: \"_start\",\n createEmpty: false\n) join(tables:{today:today, yesterday:yesterday}, on:[\"_field\"]) |> map(fn:(r) => ({ _time: r._time_today,\n _value: r._value_today - r._value_yesterday,\n })) |> aggregateWindow(\n every: 24h,\n fn: mean,\n timeSrc: \"_start\",\n createEmpty: false\n)",|
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
||

Does this work with just Grafana and InfluxDB or does it need more tools on top?

  • Alex
10

Hey, you only need to enable fluxlang in your influxdb installation and add flux datasource to grafana

2 Likes
11

It has been a while but someone was able to get it working here: Average per hour - #14 by MarioG