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.

scott
February 22, 2022, 5:45pm
#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}))
```

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:

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