Daily and Weekly power usage from avg watts per minute

Grafana InfluxDB
1

Hi,

I know this has been raised in various forms, but cant seem to get my use case and new to both so struggling to figure out query.

I’m running Home Assistant and logging to influxdb via it using a aeotec Home Enenrgy Montior (v2). It logs very simplistic as

name:"Watts"
  columns:Array[2]
    0:"time"
    1:"mean"
  values:Array[2881]
    0:Array[1521359340000,null]
    1:Array[1521359400000,3124.315]
    2:Array[1521359460000,1990.012]
    3:Array[1521359520000,855.709]
    4:Array[1521359580000,858.111]
    5:Array[1521359640000,809.255]
    6:Array[1521359700000,760.399]
    ...

To calculate last hour’s Kw’s i use basic query like:

_SELECT mean("value") FROM "Watts" WHERE time >= now() - 1h GROUP BY time(1m)_

But i cant figure out how i create a query for day / week / month.

My thoughts are that i need to create the average usage per hour and then sum that together over the perioid. Ie,

_SELECT mean("value") FROM "Watts" WHERE time >= now() - 1h GROUP BY time(1m)_
  +
_SELECT mean("value") FROM "Watts" WHERE time <= now() - 1h and time >= now() - 2h GROUP BY time(1m)_
  +
...

But i’m not sure how to remotely do that?

Any ideas and thanks
Derick

2

Hi,

Don’t you want to group by day/week/month respectively and possibly adjust the time range in Grafana?

Marcus