Daily last sample difference calculation

Fluxlang
1

Hi community!

I have finally migrated from 1.x to 2.x and are now updating my CQ to Tasks, but are a bit stuck with this one!

Background:
I store Energy in Wh from a sensor that continuous increase in size. I want every day store the consumption by taking last sample and calculate the difference, by that I get the consumption per day. The measurement I want to store to another bucket SensorData_2

InfluxQL Old CQ I used in 1.x environment
CREATE CONTINUOUS QUERY "Energy_1d_last_diff" ON "SensorData_1" BEGIN SELECT difference(last(Wh)) as Wh INTO "SensorData_2"."autogen"."Energy_1d_last_diff" FROM "Energy" GROUP BY time(1d), END

Flux

option task = {
    name: "Energy_1d",
    every: 1d,
}

from(bucket: "SensorData_1")
    |> filter(fn: (r) => r._measurement == "Energy")
    |> last()
    |> difference()
    |> set(key: "_measurement", value: "Energy_1d")
    |> to(bucket: "SensorData_2/autogen")

But getting this error message…

error exhausting result iterator: error in building plan while starting program: cannot submit unbounded read to “SensorData_1”; try bounding ‘from’ with a call to ‘range’

Please need some help here, any advice?

2

Hi @Magnus_P,
I hope you are doing well. Great to see you have moved onto Flux. So I think you’re pretty close to achieving your goal. Here is my take based on my own data:

sensor_raw = from(bucket: "Jetson")
  |> range(start: -24h)
  |> filter(fn: (r) => r["_measurement"] == "exec_jetson_stats")
  |> filter(fn: (r) => r["_field"] == "jetson_CPU1")

first = sensor_raw |> first()
last = sensor_raw |> last()

union(tables: [first, last])
   |> difference(nonNegative: false, columns: ["_value"])
   |> set(key: "_measurement", value: "Energy_1d")
   |> to(bucket: "SensorData_2/autogen")
  1. I take a range of 24 hours
  2. I store the first and last value of the 24 hour window within variables
  3. I then merge these results into one table using union.
  4. From there we can use the difference function to calculate the difference between the first and last value.
  5. Complete the set and to as you intended.

Let me know what you think :slight_smile:

3

Thanks a lot :+1: it seems to work after some test runs. I will have it active on during next coming days and review it

option task = {
    name: "Energy_day",
    every: 1d,
}

sensor_raw = from(bucket: "SensorData_1")
    |> range(start: -1d)
    |> filter(fn: (r) => r["_measurement"] == "Energy")
    |> filter(fn: (r) => r["_field"] == "Wh")

first = sensor_raw |> first()
last = sensor_raw |> last()

union(tables: [first, last])
    |> difference(nonNegative: false, columns: ["_value"])
    |> set(key: "_measurement", value: "Energy_day")
    |> to(bucket: "SensorData_2")

I still have a question though since I want to have similar measurement for month and year. I suppose I change the |> range(start: -1d), |> range(start: -1mo), |> range(start: -1y) and the corresponding option task every 1d, 1mo and 1y?

4

Hi @Magnus_P,
Exactly that! Let me know how you get on.

5

@Jay_Clifford

The task produce well but have a question about the timestamp on the daily sample. Each sample is on the new day e.g. 2021-12-06T00:00:00.000Z which actual is the consumption over 2021-12-05. Is there as possibility in a easy manner to set the timestamp to 2021-12-05T23:59:00.000Z. Otherwise the summary and visualizations using the data showing the wrong day!

image
image1487×173 4.7 KB

6

@Magnus_P You can use timeShift() to shift the timestamps back a day:

// ...

union(tables: [first, last])
    |> difference(nonNegative: false, columns: ["_value"])
    |> set(key: "_measurement", value: "Energy_day")
    |> timeShift(duration: -1d)
    |> to(bucket: "SensorData_2")
1 Like