Compute energy costs

InfluxDB 2
1

Hello,

I’m trying to compute my energy costs using InfluxDB 2 and InfluxQL.

The Tempo option by EDF offers a tiered electricity pricing based on three colors (red, blue, and white), which represent high, medium, and low-demand days, respectively. These prices also vary between peak and off-peak hours, enabling subscribers to benefit from more favorable rates by adjusting their consumption according to these time slots and day colors.

This initial query returns the daily consumption in kWh for each period:

daily_kwh = from(bucket: "energy") 
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "linky" and r["_field"] =~ /^(bleu|blanc|rouge)_(h[pc])$/)
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false)
  |> difference(nonNegative: true, columns: ["_value"])
  |> map(fn: (r) => ({
    _time: r._time,
    _field: r._field,
    _value: r._value / 1000.0,
  }))

The prices are stored in the measurement linky_cost. Each day or period will have its own tariff. The field is the same as in the linky measurement. A new record will occur with each price modification.

I would like to write a query that is based on daily_kwh, where _value would be the cost for the current line. To determine the tariff, the range start should be 0 (to indicate searching from the beginning), range stop should be the _time of the current consumption line, and we need to take the last value from linky_cost for the field equivalent to the consumption field.

This query is posing an issue for me. Could you help me or provide some guidance.

Thank you,
Thomas

PS: as a new user, I can’t send attachment, but I can provide sample data if required

2

Hi,

Currently, I did the current query:

daily_kwh = from(bucket: "energy") 
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "linky" and r["_field"] =~ /^(bleu|blanc|rouge)_(h[pc])$/)
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false)
  |> difference(nonNegative: true, columns: ["_value"])
  |> map(fn: (r) => {
    return {
      _time: r._time,
      _field: r._field,
      _value: r._value / 1000.0,
    }
  })

tempo = daily_kwh
  |> map(fn: (r) => {
    price = from(bucket: "energy")
      |> range(start: 0, stop: r._time)
      |> tableFind(fn: (key) => key._measurement == "linky_cost" and key._field == r._field + "_cost")
      |> getRecord(idx: 0)

    return {
      _time: r._time,
      _field: r._field,
      _value: r._value * price._value,
    }
  })
  |> group(columns: ["_time"])
  |> sum(column: "_value")

base = daily_kwh
  |> map(fn: (r) => {
    price = from(bucket: "energy")
      |> range(start: 0, stop: r._time)
      |> tableFind(fn: (key) => key._measurement == "linky_cost" and key._field == "base_cost")
      |> getRecord(idx: 0)

    return {
      _time: r._time,
      _value: r._value * price._value,
    }
  })
  |> group(columns: ["_time"])
  |> sum(column: "_value")

join(
  tables: {base, tempo},
  on: ["_time"]
)
  |> map(fn: (r) => ({
    _time: r._time,
    base: r._value_base,
    tempo: r._value_tempo,
    _value: r._value_base - r._value_tempo
  }))

image
image1914×459 88.8 KB

Even if InfluxDB returns rows, Grafana isn’t able to render any chart: Data is missing a time field

Any idea?

3

@rclsilver The _time column is in your results, but Grafana is looking for a time column. Can you tell Grafana what column to actually find the time values in? If not, you can rename the _time column to time with rename():

//...
    |> rename(columns: {_time: "time"})