influxQuery for "1 before last" to get full window data

Welcome & Getting Started
1

Hi guys, I’m new to influx…
I try to get some kind of candle stick pattern with Influx.
I have wrote some code and it looks nice, also get “high”, “low”, “open”, “close” and it’s kind of “aggregated” via the window function with time and period. Let’s say 3min candles.
But I just need to query the “last fully closed candle” - how can I do this - as “last()” will include the currently active candle that is not finishes as 3min window is not completed.
what can I do?

That’s the code - someone having an idea? (I hardcoded start to make sure the window is always starting at a round clock time)

open = from(bucket: "trading")
  |> range(start: 2022-12-03T20:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "quote")
  |> filter(fn: (r) => r["_field"] == "entryPriceBUY")
    |> window(every: 3m, period: 3m)
    |> first()
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)
    |> set(key: "tag", value: "open")
        |> group(columns: ["tag"])

close = from(bucket: "trading")
  |> range(start: 2022-12-03T20:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "quote")
  |> filter(fn: (r) => r["_field"] == "entryPriceBUY")
    |> window(every: 3m, period: 3m)
    |> last()
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)
    |> set(key: "tag", value: "close")
        |> group(columns: ["tag"])

low = from(bucket: "trading")
  |> range(start: 2022-12-03T20:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "quote")
  |> filter(fn: (r) => r["_field"] == "entryPriceBUY")
    |> window(every: 3m, period: 3m)
    |> min()
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)
    |> set(key: "tag", value: "low")
        |> group(columns: ["tag"])

high = from(bucket: "trading")
  |> range(start: 2022-12-03T20:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "quote")
  |> filter(fn: (r) => r["_field"] == "entryPriceBUY")
    |> window(every: 3m, period: 3m)
    |> max()
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)
    |> set(key: "tag", value: "high")
        |> group(columns: ["tag"])

union(tables: [high,low, open, close])
  |> last()

Without “last()” I get good data (see screenshot)

Bildschirm­foto 2022-12-03 um 23.02.38
Bildschirm­foto 2022-12-03 um 23.02.383652×998 469 KB

2

Edit: seems like this make the trick - but not sure if it’s correct or not… just observing the values…

...
union(tables: [high,low, open, close])
  |> range(start: -6m)
  |> last()
1 Like
3

@Richtation,
Thanks for sharing your solution!
For future reference to anyone else inquiring there are these two examples of candlestick functions

4

@Richtation It’s a little hacky, but it should work. You can use date.truncate() to truncate your range()s stop value to the same duration as your window. So you just won’t query that last window. Try this:

import "date"

windowInterval = 3m

data = () => 
    from(bucket: "trading")
        |> range(
            start: 2022-12-03T20:00:00Z,
            stop: date.truncate(unit: windowInterval, t: now())
        )
        |> filter(fn: (r) => r["_measurement"] == "quote")
        |> filter(fn: (r) => r["_field"] == "entryPriceBUY")

open = data()
    |> aggregateWindow(every: windowInterval, fn: first)
    |> set(key: "tag", value: "open")

close = data()
    |> aggregateWindow(every: windowInterval, fn: last)
    |> set(key: "tag", value: "close")

low = data()
    |> aggregateWindow(every: windowInterval, fn: min)
    |> set(key: "tag", value: "low")

high = data()
    |> aggregateWindow(every: windowInterval, fn: max)
    |> set(key: "tag", value: "high")

union(tables: [high,low, open, close])
    |> group(columns: ["tag"])