Would you be able to share your data for that three day period?
This query will give you the mean of the field
value for the past three days for the measurement
db0.autogen.power and the tag
deviceName with the value
If your data if collected at regular intervals (uniformly distributed in time), then calculating the mean of the values will also give you the average power usage over time.
If your data is collected at irregular intervals, then calculating the mean of the values will not give you the expected results. As you mentioned that you had dropped values and were using
fill(0), I suspect this could be a possible explanation for the results you’re seeing.
Let’s look at an example. I’m using the Influx CLI. I’ve inserted a number of values into a measurement called
> select * from m1 where time > '2018-08-14T17:22:14Z' and time < '2018-08-14T17:23:20Z'
Since I inserted the data manually, the values are not evenly spaced. The first four occur during the same minute, while the fifth occurs nearly a minute later. I can calculate the mean of these values:
> select mean(*) From m1
And we get 30, as expected. The timestamp value is the beginning of the time window for which you’re calculating the mean. But, as you pointed out before, the duration of the measurement matters to the final result. This is what those points look like on a graph:
The majority of the time is spent between the value of 40 and the value of 50; we’re interpolating linearly between those points to draw a graph. The mean over time, therefore, is probably closer to 40 than it is to 30.
We can’t take the mean of the values for a series with irregularly spaced values, but instead what we can do is break up our time range into discreet units using the
GROUP BY clause. This is what our data points look like grouped into ten second intervals:
> select mean(*) from m1 where time > '2018-08-14T17:22:14Z' and time < '2018-08-14T17:23:20Z' group by time(10s)
Because we’re potentially grouping values together, we need to provide a function for the database to apply when there are multiple values within the same window—in this case, we’re using
mean(). So for the 10-second period beginning 2018-08-14T17:22:10Z, we have three values, 10, 20, and 30, and the mean of those is 20. For the next window, we have a single value, and then we have a number of windows with no value before we see the final value of 50.
We can fill in those empty windows using
fill() option of
GROUP BY. This is what our data looks like using
select mean(*) from m1 where time > '2018-08-14T17:22:14Z' and time < '2018-08-14T17:23:20Z' group by time(10s) fill(previous)
We can calculate the mean of this data using subqueries:
select mean(*) from (select mean(*) from m1 where time > '2018-08-14T17:22:14Z' and time < '2018-08-14T17:23:20Z' group by time(10s) fill(previous))
There’s another caveat: the window that we choose will have an impact on our final results. What if we use a 1s window instead of a 10s one?
> select mean(*) from (select mean(*) from m1 where time > '2018-08-14T17:22:14Z' and time < '2018-08-14T17:23:20Z' group by time(1s) fill(previous))
Assuming that this is related to the issue you’re having, a query like this one might provide more accurate results:
SELECT mean("mean_value") FROM
(SELECT mean("value") AS "mean_value" FROM "db0"."autogen"."power" WHERE time > now() - 3d AND "deviceName"='3D Printer' GROUP BY time(10s) FILL(previous))
You will have to decide which time window and fill mode are appropriate for you.