Past min and max values

[po220]

Hello

I am trying to display a panel like this one

with
in yellow the values of a “test” time series
in blue the max and in purple the min of the same “test” but on average over the last 20 weeks

In this example, “Test” would have the value 23 at 8 a.m. and the average minimum of the values of “Test” at 8 a.m. over the last 20 weeks is 20 and the maximum is 34.

to retrieve the “test” values no problem I use this :

from(bucket: "M")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "modbus")
|> filter(fn: (r) => r._field == "test")
|> keep(columns: ["_time", "_field", "_value"])
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: "mean")

but how to retrieve time series with the min and max values over the last 20 weeks?

Thanks in advance

[grant2]

This should help you get started.

import "system"
import "time"

// Calculate the start time for 20 weeks ago
// You can adjust 'now()' to a specific timestamp if needed for testing historical data
start20WeeksAgo = time.truncate(t: time.add(d: -20w, to: now()), unit: 1s)

from(bucket: "M")
  |> range(start: start20WeeksAgo, stop: now()) // Changed start and stop to cover last 20 weeks
  |> filter(fn: (r) => r["_measurement"] == "modbus")
  |> filter(fn: (r) => r._field == "test")
  |> keep(columns: ["_time", "_field", "_value"])
  |> aggregateWindow(every: 1d, fn: min, createEmpty: false) // Example: daily min -- change as needed
  |> yield(name: "daily_min")

from(bucket: "M")
  |> range(start: start20WeeksAgo, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "modbus")
  |> filter(fn: (r) => r._field == "test")
  |> keep(columns: ["_time", "_field", "_value"])
  |> aggregateWindow(every: 1d, fn: max, createEmpty: false) // Example: daily max -- change as needed
  |> yield(name: "daily_max")

[po220]

Hello and thank you very much

import “time” give me error “invalid import path time” so I tested without it this

import "system"

from(bucket: "M")
  |> range(start: -20w , stop: now()) 
  |> filter(fn: (r) => r["_measurement"] == "modbus")
  |> filter(fn: (r) => r._field == "test")
  |> keep(columns: ["_time", "_field", "_value"])
  |> aggregateWindow(every: 10m, fn: min, createEmpty: false) 
  |> yield(name: "test_min")

from(bucket: "M")
  |> range(start: -20w, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "modbus")
  |> filter(fn: (r) => r._field == "test")
  |> keep(columns: ["_time", "_field", "_value"])
  |> aggregateWindow(every: 10m, fn: max, createEmpty: false) 
  |> yield(name: "test_max")

It works, but I have a few problems.

1. The new names “test_min” and “test_max” are not taken into account; both parameters appear as “test” on the graph.
2. My server load is increasing dramatically with this.
   Isn’t it a very bad solution to calculate this history at each refresh? Shouldn’t I find a way to calculate and store the history so that it doesn’t have to be recalculated each time?
   which way ?
   How can I get the same kind of result with more lightness?
   I’m using Telegraf to collect the data. Is there a way I can create the minimum and maximum historical values for each “test” point at the time of recording?

Thanks

[grant2]

@po220

import “time” give me error “invalid import path time” so I tested without it this

That was my mistake…I thought that you needed a package called “time” to do the truncate time function, but you figured out a way.

@po220

The new names “test_min” and “test_max” are not taken into account; both parameters appear as “test” on the graph.

insert this line after each keep function:

|> rename(columns: {_field: "test_min"})  // change to test_max for the second query

@po220

Shouldn’t I find a way to calculate and store the history so that it doesn’t have to be recalculated each time?
which way ?

Sure, you can calculate the min and max anytime you wish and store them in the database. But I feel that that shouldn’t be necessary unless you have a REALLY large number of data points every 20 weeks. About how many do you have?

You can also create a Flux task to downsample your data every n minutes (hours, days, etc.). More here:

Downsample data in an InfluxDB task | InfluxDB OSS v2 Documentation

@po220

I’m using Telegraf to collect the data. Is there a way I can create the minimum and maximum historical values for each “test” point at the time of recording?

Probably best to as that question on the InfluxDB forum.

[po220]

@grant2

About how many do you have?

I have a value every 10s
the 20th week is arbitrary, it could be 10 or 52, but I have 8640 points per day.

Thanks for downsample task idea, I will study this very interesting solution