easy
January 2, 2023, 7:03am
1
Hello,

is there a simple way, to calculate the usage per real day?

What I mean is, I have a db where entries are very spordic.
e.g. for the last 30 days, I have an day 1, 6, 9, 12, 13, 17, 21 and 26 entries in my db.

I can now calculate a daily average as I use the sum of the entries divided by 30.

But I want to have a usage per “real” day. In this example I would divide the sum by 8 (due to the days, where are entries).

Is there a way, to do this in a flux-Query?

Regards,
easy.

grant1
January 2, 2023, 1:04pm
2
Hi @easy and welcome to the forum.

Are days like 2, 5 and 15 completely blank, or with zero values, or some other discernible feature?

easy
January 2, 2023, 1:35pm
3
Hello @grant1 and thanks.

they are filled with zeros, data is coming from a simple smart plug meter using grafana for visualiztion.

Currently I’m using following query for the average of the last 30 days: (The map is using due to lack of grafana to label a output “correctly” and for 3 digits after decimal point):

import “math”

from(bucket: “telegraf”)
|> range(start: -30d)
|> filter(fn: (r) => r[“_measurement”] == “tasmota”)
|> filter(fn: (r) => r[“_field”] == “Today”)
|> filter(fn: (r) => r[“sensor_id”] == “smartplug”)
|> aggregateWindow(every: duration(v: 1d), fn: spread, createEmpty: false)
|> mean()
|> map(fn: (r) => ({r with _value: “Tages-Ø: “+string(v: math.round(x: r._value * 1000.0) / 1000.0)+” kWh”}))

I “just” want to exclude the days where no power is consumed to get a “real” average.

Reagards.

grant1
January 2, 2023, 3:45pm
4
@easy

I do not have any opportunity to test this, but I believe you can simply add a `filter()`

function before the `aggregateWindow()`

function.

```
|> filter(fn: (r) => r._value > 0, onEmpty: "drop")
```

Can you try it out and see if it gives you the correct result?