Include InfluxDB prices in electricity consumption

Grafana InfluxDB
1

  • What Grafana version and what operating system are you using?
    9.4.7

  • What are you trying to achieve?

I am currently in the process of setting up a dashboard for my PV system.
I try to make sure that all data in the selected time period are correct.
However, I am still unsure about the design when calculating the prices.
Since the prices will probably change in the next few years, I have to use a different price depending on the time period in which the consumption was measured.
I was thinking of creating a bucket which has a start and end time in addition to the electricity price.
But how can I use this ?
Example Querry to get measurment data:

  from(bucket: "ioBroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "senec.0._calc.LIVE_HOUSE_CONS.today")
  |> filter(fn: (r) => r["_field"] == "value")
  • How are you trying to achieve it?

I tried to query the data with union and pivot. The goal was to get a table with the columns “KW”, “buixingPrice”, “sellingPrice”.
However, this does not really work.

import "contrib/tomhollingworth/events"
import "date"

price = from(bucket: "PV_Costs_Income")
  |> range(start: date.truncate(t: v.timeRangeStart, unit: 1d), stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "sellingPrice" or r["_measurement"] == "buyingPrice")
  |> filter(fn: (r) => r["_field"] == "value")
  |> drop(columns: ["_field"])

kw = from(bucket: "ioBroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "senec.0.ENERGY.GUI_GRID_POW")
  |> filter(fn: (r) => r["_field"] == "value")
  |> truncateTimeColumn(unit: 1d)
    |> drop(columns: ["_field"])
    
union(tables: [price, kw])
   |> pivot(rowKey: ["_time"], columnKey: ["_measurement"], valueColumn: "_value")