Calculate Energy Cost between 2 Dates

[matze1708]

Hallo,

ich möchte Energie kosten errechnen. Ich habe in der Datenbank immer den jeweiligen aktuellen Zählerstand.

Dann müsste ich die Differenz zwischen dem ersten Tag vom Jahr und Heute errechnen und diese mit dem Wert multipliziert.

Wie stelle ich das an?

import "date"

month = date.truncate(t: now(), unit: 1mo)

from(bucket: "VH_Messwerte")
  |> range(start: month, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "mqtt_consumer")
  |> filter(fn: (r) => r["topic"] == "Energie/VH/Zaehlerstand/aktuell")
 
  |> derivative(
    unit: 1m,
    nonNegative: false
  )
  |> map(fn: (r) => ({
    r with
    _value: (float(v: r._value) * 0.29 ) 
  }))

  |> aggregateWindow(every: 1d, fn: mean, createEmpty: false)
  |> yield(name: "last")

Thank you

[Anaisdg]

Hello @matze1708,

I translated your German, but I’m still having trouble understanding what you mean. Could you provide example data and your desired output?

However to calculate the difference between two values at two different timestamps you could do the following:

Assuming:

firstDay =  from(bucket: "VH_Messwerte")
|> range(start: firstDayTimestamp)
|> first()

lastDay = from(bucket: "VH_Messwerte")
|> range(start: lastDayTimestamp)
|> first()

You could:

1. perform a union

union(tables: [firstDay, lastDay])
|> difference()

2. use findRecord() to extract values

firstDayValue = firstDay 
|> findRecord(fn: (key) => key.mytag == "t0", idx: 0)

lastDayValue = lastDay
|> findRecord(fn: (key) => key.mytag == "t0", idx: 0)

lastDay
|> map(fn: (r) => ({ r with difference: firstDayValue._value - lastDayValue._value}))

Here’s a blog on findRecord():

TL;DR InfluxDB Tech Tips – How to Extract Values, Visualize Scalars, and...

3. Perform a join()

join(tables: {lastDay: lastDay, firstDay: lastDay}, on: ["_time", "_field"], method: "inner")
|> map(fn: (r) => ({ r with difference: r.firstDay_value - r.lastDay_value}))

Does that help?

[matze1708]

Hello,
Thanks for the message, sorry I didn’t seem to realize I copied the wrong text from DeepL.

I want to determine the value from the first day of the year to today

So always 1.1. to today
In the database is the e.g. the value 27000 to the 1.1 on today it is 28600
These differences I would like to calculate and multiply with the costs of the energy.

[matze1708]

I am looking for the solution to calculate my consumption based on a meter reading.

For this I would need to get the first entry in the year.
Then the most recent
These two would have to be subtracted and this result would be the current consumption in the year.
I would then multiply this with a value to display the costs.

But how do I do that?

I find this InfluxDB 2.0 totally complicated. A pure SQL would be better for me.

Thank you