Take difference of current value and value one week ago (Ever increasing data)

Fluxlang
,
1

I am monitoring an energy usage meter that is constantly increasing. I now want to generate three time series that take the current value and subtract the value from 1d, 7d, and 30d ago.

I have tried the spread() function, which however only outputs a number instead of a time series.

I am using influxdb 2 (flux language) and grafana. I am not sure if it is a visualzation problem or if I have to generate a new data field in influxdb.

Thanks in advance.

2

@Globgogabgalab You’ll need to essentially run 4 separate queries, union them together, adjust the timestamps so they align to the same time, then pivot the values into each row. For example:

import "experimental"

current = from(bucket: "example-bucket")
    |> range(start: -1h, stop: now())
    |> filter(fn: (r) => r._field == "someField")

dayAgo = current = from(bucket: "example-bucket")
    |> range(start: -25h, stop: -24h)
    |> filter(fn: (r) => r._field == "someField")
    |> timeShift(duration: 24h)
    |> map(fn: (r) => ({ r with _field: r._field + "_24h" }))

weekAgo = current = from(bucket: "example-bucket")
    |> range(start: -7d1h, stop: -7d)
    |> filter(fn: (r) => r._field == "someField")
    |> timeShift(duration: 7d)
    |> map(fn: (r) => ({ r with _field: r._field + "_7d" }))

monthAgo = current = from(bucket: "example-bucket")
    |> range(start: -30d1h, stop: -30d)
    |> filter(fn: (r) => r._field == "someField")
    |> timeShift(duration: 30d)
    |> map(fn: (r) => ({ r with _field: r._field + "_30d" }))

union(tables: [current, dayAgo, weekAgo, monthAgo])
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> map(fn: (r) => ({r with 24hDiff: r.someField - r.someField_24h, 7dDiff: r.someField - r.someField_7d, r with 30dDiff: r.someField - r.someField_30d}))
3

Thank you very much for the reply. I tried your solution but ran into some error.

import "experimental"

current = from(bucket: "CTA_STATS")
  |> range(start: -1h, stop: now())
  |> filter(fn: (r) =>
    r.id == "3139" and
    r._field == "val"
    )

dayAgo = from(bucket: "CTA_STATS")
  |> range(start: -25h, stop: -24h)
  |> filter(fn: (r) =>
    r.id == "3139" and
    r._field == "val"
    )
  |> timeShift(duration: 24h)
  |> map(fn: (r) => ({ r with _field: r._field + "_24h" }))

union(tables: [current, dayAgo])
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({r with 24hDiff: r.val - r.val_24h}))

I receive this error:

 compilation failed: error at @22:30-22:52: unexpected token for property key: DURATION (24h)

Do you know what is wrong with my code?

My data looks as follows:

image
image1597×543 78.2 KB

4

Yes, I have a similar data need. I get the same error.