Flux group by time


I am trying to downsample data to get candlesticks in a flux script. The data is simple and its just t_time, price and i would like the output to be _time, open, high,low,close sampled by minute.
Its odd to me since it doesn’t look like there is group db time functionality in flux. Does anyone know if this is possible of if there is a work around to this. If i could down sample the time column and join it with the data even that would work.



@Mark_Best This is what window() is for. It creates windows of time at a specified duration. So if you want to group points into 1 minute windows, you’d do:

  |> window(every: 1m)

I’d recommend using aggregateWindow() to both window and aggregate your data.

Lucky for you, I actually wrote a custom candlestick() function a while back which looks like it’ll be perfect for you:

candlestick = (tables=<-) => tables
  |> reduce(
    fn: (r, accumulator) => ({
      index: accumulator.index + 1,
      open: if accumulator.index == 0 then r._value else accumulator.open,
      close: r._value,
      high: if accumulator.index == 0 then r._value else if r._value > accumulator.high then r._value else accumulator.high,
      low: if accumulator.index == 0 then r._value else if r._value < accumulator.low then r._value else accumulator.low
    identity: { index: 0, open: 0.0, close: 0.0, high: 0.0, low: 0.0 }
  |> drop(columns: ["index"])

// --- Adjust below as necessary ---
from(bucket: "bucketName")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "measurementName" and r._field == "price")
  |> aggregateWindow(
    every: 1m,
    fn: (tables=<-, column) => tables |> candlestick()

yes, this is exactly what i was looking for. You also saw ahead what i was trying to do. I am really new to flux so i’m going to have to look a bit more into the syntax but your reply has sent me in the right direction.

@Mark_Best This is taking you right into the deep end in some intermediate-to-advanced scripting, but I essentially create a custom aggregate function that outputs the open, close, high, and low for each input table. The Custom aggregate functions doc walks through how reduce() works. I then use the custom function to aggregate all the time-based groups generated from window() inside of aggregateWindow().

That’s a brief synopsis. If you have specific questions about what it’s doing, let me know. I’m happy to answer them.