Daily power consumption profile graph

influxdb
grafana

#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:

Regards,

Bill