Date-time of the min and max values measured using aggregateWindow

1

I have this simple raw data:

timestamp field1 field2 tags
2025-02-20T06:20:00Z 10.0 40.0 “tag1”: “v100”
2025-02-20T06:24:00Z 20.0 30.0 “tag1”: “v100”
2025-02-20T06:28:00Z 30.0 20.0 “tag1”: “v100”
2025-02-20T06:32:00Z 40.0 10.0 “tag1”: “v100”

My question is:
How can I get the exact date-time of the min and max values measured using aggregateWindow?

The desired output:

field function time value
field1 min 2025-02-20T06:20:00Z 10.0
field1 max 2025-02-20T06:32:00Z 40.0
field2 min 2025-02-20T06:32:00Z 10.0
field2 max 2025-02-20T06:20:00Z 40.0

This is my flux script: for just getting the max. Once I get the associated timestamp then I will union the min and max.

from(bucket:“bucket001”)
|> range(start:2020-03-17T00:00:00.000000000Z, stop:2025-03-18T00:00:00.000000000Z)
|> filter(fn: (r) => (r[“_measurement”] == “main” and r[“id”] == “32373957-4186-4374-a925-c0911cf193f6”))
|> sort()
|> aggregateWindow(every:1h, fn:max, timeSrc:“_start”, createEmpty:false)
|> toFloat()
|> group()

2

Hello @m0hammed,
Here ya go

import "array"

data = array.from(
    rows: [
        {_time: 2025-02-20T06:20:00Z, _field: "field1", _value: 10.0, tag1: "v100"},
        {_time: 2025-02-20T06:20:00Z, _field: "field2", _value: 40.0, tag1: "v100"},
        {_time: 2025-02-20T06:24:00Z, _field: "field1", _value: 20.0, tag1: "v100"},
        {_time: 2025-02-20T06:24:00Z, _field: "field2", _value: 30.0, tag1: "v100"},
        {_time: 2025-02-20T06:28:00Z, _field: "field1", _value: 30.0, tag1: "v100"},
        {_time: 2025-02-20T06:28:00Z, _field: "field2", _value: 20.0, tag1: "v100"},
        {_time: 2025-02-20T06:32:00Z, _field: "field1", _value: 40.0, tag1: "v100"},
        {_time: 2025-02-20T06:32:00Z, _field: "field2", _value: 10.0, tag1: "v100"}
    ]
)
|> range(start: 2025-02-20T06:20:00Z, stop: 2025-02-20T06:33:00Z)
|> group(columns: ["_field"])  // Ensure aggregation happens per field

min = data
  |> min() 
  |> map(fn: (r) => ({_field: r._field, function: "min", time: r._time, value: r._value}))
  |> yield(name: "min")

max = data
  |> max()
  |> map(fn: (r) => ({_field: r._field, function: "max", time: r._time, value: r._value}))
  |> yield(name: "max")

ofc you can replace wtih agg window just know that the aggregateWindow() function in Flux creates time-based windows of data and applies an aggregate function to each window. By default, window boundaries are aligned with UTC time (00:00:00), but you can control this alignment in several ways.

To align with start do something like

from(bucket: "example-bucket")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "cpu" and r._field == "usage_system")
  |> aggregateWindow(every: 5m, fn: mean, offset: duration(v: int(v: v.timeRangeStart) % int(v: 5m)))
The offset calculation takes the modulus of your range start time with the window duration, effectively aligning the windows with your range start.
3

I’m not sure how this solve this case to get date-time of the data point. as you mentioned, there is a reason for using aggregateWindow function. I just simplified the script to describe my needs. I still need the start and stop times of the window as well as the date-time of the min/max data point, given a historical data of X years and the users want to aggregate per year, month, day, … intervals.

4

Does the script I gave you above with aggregate window not work? How does it differ from your expected output?

5

Hi @m0hammed

Check out this dashboard that I created: https://play.grafana.org/d/e7bad3ef-db0c-4bbd-8245-b85c0b2ca2b9/389d020a-e36b-58aa-a626-3b37e03730bd?orgId=1&from=now-30d&to=now&timezone=browser&var-region=$__all&var-type=Demand&var-fueltype=$__all&refresh=1m&editPanel=7

It displays the Date & Time of the max value. As you change the Time Picker (last 30 days, last 2 years, last 5 years, etc.) you will get different values the table. Maybe it will give you some ideas.

1 Like
6

I didn’t look at it in details but your script won’t work. You change the aggregation function mean while I’m asking for min/max and per the documentation, the offset is a “Duration to shift the window boundaries..” while I’m looking the exact timestamp of the data point.

The script I provide doesn’t work either that’s why I’m here to ask for help. I need the start and stop timestamps of the window as well as the timestamp of the min/max data point.

7

This won’t give the exact data point timestamp. My understanding is that your script is downsampling then the max function will get one of the time boundaries as timestamp. I could be wrong though.

My need is, while downsampling, I want the window timestamps boundaries as well as the data point’s timestamp.

I don’t think this is possible using Flux. There is a work around but it will require a major change to our code base.

Thanks everyone for all your suggestions!

8

Hello @m0hammed,
I provided the general logic, you can supplement aggwindow for min or max. Agg window will always give you the timestamps of the windows created by it.
You can use window() funciton first to window your data which will create tables/groups based off of that. Then you can apply min and max functions within those windows.