How to use candlestick visualization

Hello
i have some measurements about temperature all 15minutes new data.
i would like to create a Candelstick daily basis - but i only get a dotet line


have a nice day

Hi @vikozo,

Thanks for opening this post.

Did you already check the documentation regarding it i.e.

Also, found a very detailed explanation as how to use it for time-series data via this link and even though you are using InfluxDB, will still give you good information as how to make it work.

I hope this helps.

The second link does not point to the candlestick tutorial anymore.
The first link does not provide any information how to setup candlestick charts with InfluxDB. i.e. there is no information how to provide high,low,close to the chart by using Flux queries. All attempts i have tried failed.
May be someone could tell the magic?

Welcome @docb

Are you using influxql or flux query language?

hi, i am using flux as i have installed infludb2 and it seems that there is no other choice.

try something like this

from(bucket: "market")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "market")
  |> filter(fn: (r) => r["_field"] == "close" or r["_field"] == "high" or r["_field"] == "low" or r["_field"] == "open" or r["_field"] == "volume")
   |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")  
  |> drop(columns: ["_start", "_stop", "_measurement", "type"])

so the magic is, that it is expected that the there are fields “close” “open” etc in the line returned from influx? this is not the case, there is only a “price” value.
e.g. the high field is obtained by:
from(bucket: “crypto”)
|> range(start: 2023-11-04T12:20:00Z)
|> filter(fn: (r) => r._measurement == “ethusd”)
|> aggregateWindow(every: 1m, fn: max)
so the question is how to tell the chart that this is the high value
– and same with low, close and open (for low - fn: min, for cloese - fn:last, for open - fn: first)

please post the schema of your bucket first.

nothing special:
in the measurement is one field “price” and one tag “currency=usd”

No dates?

sure as in every measurement there is a timestamp

you can do just usd but it will be a barchart

sorry i don’t get what you are saying.

How often do you write data to your bucket?
If you have only 1 field, in order to get open,low,high,close you will obviously need 4 days worth of data if not mistaken

Or more than one entry per day

Currently i am writing every second, here you see the open,close,high,low in the chart top-left. The same settings are in the chart on the right side but it is of type candlestick. Magically there is now the high low line displayed but no body.
Below there is the normal timeseries displayed.

1 Like

Hi @docb

In your graph above where you are displaying open/close/high/low, you are actually plotting 4 separate Flux queries, correct? Maybe share the text of each query here?

Second, as far as I can tell, the Candlestick visualization needs you to map those 4 values (open/close/high/low) into these boxes. Again, please share what you have populated here.

And inspecting your query should look something like this:

1 Like

@grant2 I tried to do this in one flux query using multiple data catpures

low=Data
|> min()

Etc

And then union(open,low,high,close)

And got stuck there when I tried map()

like this?

These queries give the top left panel in the image above where the display name is overridden.

Query A display override “close”

from(bucket: "forex")
    |> range(start: 2023-11-04T12:20:00Z)
    |> filter(fn: (r) => r._measurement == "ethusd")
        |> aggregateWindow(every: 1m, fn: last)

Query B display override “open”

from(bucket: "forex")
    |> range(start: 2023-11-04T12:20:00Z)
    |> filter(fn: (r) => r._measurement == "ethusd")
        |> aggregateWindow(every: 1m, fn: first)

Query C display override “high”

from(bucket: "forex")
    |> range(start: 2023-11-04T12:20:00Z)
    |> filter(fn: (r) => r._measurement == "ethusd")
        |> aggregateWindow(every: 1m, fn: max)

Query C display override “low”

from(bucket: "forex")
    |> range(start: 2023-11-04T12:20:00Z)
    |> filter(fn: (r) => r._measurement == "ethusd")
        |> aggregateWindow(every: 1m, fn: min)

Unfortunately in every attempt to map these queries to the candlestick fields failed.
There is no appropriate choice in the selection and modifying by hand also does not work.

Pretty sure that a union of the 4 queries and then a pivot function would be needed (to get the last, first, open, close into their own columns for each timestamped record).

close = from(bucket: "forex")
    |> range(start: 2023-11-04T12:20:00Z)
    |> filter(fn: (r) => r._measurement == "ethusd")
        |> aggregateWindow(every: 1m, fn: last)

open = from(bucket: "forex")
    |> range(start: 2023-11-04T12:20:00Z)
    |> filter(fn: (r) => r._measurement == "ethusd")
        |> aggregateWindow(every: 1m, fn: first)

etc.

union(tables: [close, open, low, high])
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value") // modify as needed

this also didn’t work, i wonder how to get some working assignment in the right menu on the screenshot below. how are the fields mapped to the candlestick fields? still magic.