Influx 2.0 value and time pairs to display (ignoring influx timestamp)

Grafana InfluxDB
1

Hello

I have a question, i get some data from the solar pannel which i cant change the format of into my influxdb. Here is an example, to test i was trying only with 1 Datapoint:

from(bucket: “b_iobroker”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] =~ /plenticore.0.forecast.day1.power.9h.(power|time)$/)
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every: 24h, fn: last)
|> yield(name: “forecast”)

response:Object
  results:Object
    B:Object
      frames:Array[2]
        0:Object
          schema:Object
          data:Object
            values:Array[2]
              0:Array[1660057637958]
              1:Array[3460.0666666666666]
        1:Object
          schema:Object
          data:Object
            values:Array[2]
              0:Array[1660057637958]
              1:Array[1660047733953]
      refId:"B"

So basicly as you can see i have a structure
…power.9h.time.value
…power.9h.power.value

Now what i would like to do is draw the …power.9h.power.value in a graph with …power.9h.time.value as timestamp to display it at the rigth time in the graph.
In the end i need to have points/bars spaced by 1h, each with hes power value.

I use Grafana 8.4.3 with influxdb 2.0 flux syntax

Anyone got a solution, or perhaps an other aproach to get those values displayed?

2

Welcome

Though showing us your flux query is helpful what would be more helpful is if you provided us a sampling of your data for ex in inline csv format

_time,measurement,field,value
2022-08-09 18:00:00,b_iobroker,kitty,23.5

etc

That way we can have your data in our influxdb.

3

Hey

Sorry first time i have to do an export. trying to get the data exported but allways get 0kb csv… working on it :wink:

1 Like
4

hello,
i notice few things,
first :
this is the same issue in this post,
https://localhost:3000/t/table-with-influx-2-0/69818/10
i mean measurement is used as a field , and field is used as value.

second most important:
I notice you store a time field in a time series database.
if you look at any query result in influxdb you can notice every value have already a _time field , (even your time field have a time value ). this is one of the key concept of time series db.

I suggest you to stop store a time field and change your struct if you can.
if you can’t change your struc i can still help to make this working

5

you can try this:

from(bucket: “b_iobroker”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == "plenticore.0.forecast.day1.power.9h.power")
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every: 1h, fn: last)
|> group(columns :["_time"])
   |> pivot(rowKey:["_time"], columnKey: ["_measurement"], valueColumn: "_value")
|> group()
|> yield(name: “forecast”)
6

Hi alexandrearmand

First thx for your replay.
Sadly i cant change the format of the data that are stored. To give a bit context of the data:
Its solar power forecast, it gets updated regulary and gives informations about the power i probably will produce in the next 2 days.
It generates (in iobroker) following structure:

day1
	power
		1h
			power
			time
		2h
			power
			time
		3h
			power
			time
		4h
			power
			time

Where as the 1h, 2h etc is the time +offset of the sunrise that day, in the time field i have that value.
lets say the sun rises at 7:14am, then in the 1h i have the power forcast for 7:14am-8:14am

And what i would like to do is show that as a graph with the values of the forcast on the 24h timeline.

So as you see the timestamp of the moment in which the data is written to the db is irrelevant for me. i just need to be able to show the power value and time value of those two fields.

I tried your code, but it seems like it also takes the timestamp to position the power value and not the time field.

(still not managed to create a csv)

7

Ok i understand now, since it’s in the future you can’t insert it at the right timestamp ,
and day/hours are in the measurement name you need to get all measurement named as : “plenticore.0.forecast.day*.power..”
can make a csv or screenshot of your data in influxdb ? go to your localhost:8086, explore tab
and use this query

from(bucket: “b_iobroker”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == "plenticore.0.forecast.day1.power.9h.power")
|> filter(fn: (r) => r[“_field”] == “value”)

image

8

Ah nice… that works (befor that i tried hard with the influx ci to get the export, this is easier)
Here you go, i exported the time and power values. Its only the last one of the power values that will be relevant. Time is not updated befor tomorrow as it stays the same.

#group,false,false,true,true,false,false,true,true
#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,double,string,string
#default,mean,,,,,,,
,result,table,_start,_stop,_time,_value,_field,_measurement
,,0,2022-08-07T18:47:44.847636723Z,2022-08-09T18:47:44.847636723Z,2022-08-09T13:10:00Z,3665.575,value,plenticore.0.forecast.day1.power.9h.power
,,0,2022-08-07T18:47:44.847636723Z,2022-08-09T18:47:44.847636723Z,2022-08-09T13:40:00Z,3171.5750000000003,value,plenticore.0.forecast.day1.power.9h.power
,,0,2022-08-07T18:47:44.847636723Z,2022-08-09T18:47:44.847636723Z,2022-08-09T13:50:00Z,3460.0666666666666,value,plenticore.0.forecast.day1.power.9h.power
,,1,2022-08-07T18:47:44.847636723Z,2022-08-09T18:47:44.847636723Z,2022-08-09T13:10:00Z,1660047733953,value,plenticore.0.forecast.day1.power.9h.time

image
image1800×745 101 KB

1 Like
9

create a new unique key column to group by day and hours

from(bucket: “b_iobroker”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] =~ /plenticore.0.forecast.day*.power.*h.(power|time)$/)
|> filter(fn: (r) => r[“_field”] == “value”)
|> map(fn: (r) => ({ r with key_hours: strings.joinStr(arr: [strings.split(v: r._measurement, t: ".")[3], strings.split(v: r._measurement, t: ".")[5]], v: ".")}))
|> map(fn: (r) => ({ r with key_column: strings.split(v: r._measurement, t: ".")[6]}))
|> group(columns :["key_hours","key_column"])
|> pivot(rowKey:["key_hours"], columnKey: ["key_column"], valueColumn: "_value")
|> group()
|> yield(name: “forecast”)

something like that but your struct is too hard, you can’t stay like this.
since you create new measurement each hour this will not last long time… database is limited in unique aggregation key , you will have to change at somepoint
chek on this:

1 Like
10

little update, I looked into the tags you mentioned. I have set now a tag for each time and power value im interessed in, as i only need the last for each timeframe its working so far.
This query draws a point at the rigth time and rigth power:

time10s = from(bucket: "b_iobroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "forecast_time_d1_h10")
  |> filter(fn: (r) => r["_field"] == "value")
  |> map(fn: (r) => ({_time: r._time, _value: r._value *1000.0*1000.0, location: "d1_h10"}))
  |> last()
  |> toInt()
  |> toTime()

power10s = from(bucket: "b_iobroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "forecast_power_d1_h10")
  |> filter(fn: (r) => r["_field"] == "value")
  |> map(fn: (r) => ({_time: r._time, _value: r._value, location: "d1_h10"}))
  |> last()
  
join(tables: {time10: time10s, power10: power10s}, on: ["location"])
    |> map(fn: (r) => ({_time: r._value_time10, _value: r._value_power10}))
    |> aggregateWindow(every: 1m, fn: mean)
    |> yield(name: "forecast")

For now i would need to create a query for each of the 15 1h values, trying a bit the different possibilies to see what will work best. thanks for your help

11

Great ! wat a fast learner !
that is a better structure ! not ideal but still better.
i believe you still have your “hour” information in your measurement name

 |> filter(fn: (r) => r["_measurement"] == "forecast_power_d1_h1")

to get all hour from your first day you can use regex with a wildcard character
something like that (for all power) i think :

|> filter(fn: (r) => r[“_measurement”] =~ /forecast_power_d1_h*/)

but you are close to final answer !