# Compute energy costs

**URL:** <https://community.influxdata.com/t/compute-energy-costs/32443>\
**Category:** InfluxDB 2\
**Created:** [December 9, 2023, 4:51am UTC](https://community.influxdata.com/t/compute-energy-costs/32443 "2023-12-09T04:51:39Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![rclsilver](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/rclsilver/32/14730_2.png) [@rclsilver](https://community.influxdata.com/u/rclsilver)\
**Post date:** [December 9, 2023, 4:51am UTC](https://community.influxdata.com/t/compute-energy-costs/32443/1 "2023-12-09T04:51:39Z")

</div>

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:

```auto
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

---

<div class="post-metadata">

**Author:** ![rclsilver](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/rclsilver/32/14730_2.png) [@rclsilver](https://community.influxdata.com/u/rclsilver)\
**Post date:** [December 11, 2023, 1:21pm UTC](https://community.influxdata.com/t/compute-energy-costs/32443/2 "2023-12-11T13:21:57Z")

</div>

Hi,

Currently, I did the current query:

```auto
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](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/5/5fc4abab8adc204f6e3eab5d2aaddd6ab768c7a2.png)

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

Any idea?

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [December 11, 2023, 4:29pm UTC](https://community.influxdata.com/t/compute-energy-costs/32443/3 "2023-12-11T16:29:57Z")

</div>

@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()`:

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

```