Calculate Energy Cost between 2 Dates

#1

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

#2

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()
  1. 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():

  1. 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?

#3

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.

#4

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

#5

hi, did you mean this?

firstDayTimestamp = 2020-01-01T00:00:00.344603061Z
lastDayTimestamp = 2020-12-31T23:59:59.344603061Z
 
 
  firstDay =  from(bucket: "VH_Messwerte")
|> range(start: firstDayTimestamp)
|> filter(fn: (r) => r["topic"] == "Energie/VH/Zaehlerstand/aktuell")
|> first()

lastDay = from(bucket: "VH_Messwerte")
|> range(start: lastDayTimestamp)
|> filter(fn: (r) => r["topic"] == "Energie/VH/Zaehlerstand/aktuell")
|> first()

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}))

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

But i still get an error.

error in building plan while starting program: found more than one call to yield() with the name “_result”

#6

Hi,
I have played around a bit.
Is this what I need from the query?
From the starting point, count the value up to now and output and multiply by cost?!

expression=
from(bucket:"VH_Messwerte")
  |> range(start: -19d)
  |> filter(fn: (r) =>
    r["topic"] == "Energie/VH/Zaehlerstand/aktuell"
  )
    
   |> count()
  |> keep(columns: ["_value"])
 

Gesamt = expression
  |> map(fn: (r) => ({r with _value: (float(v: r._value) / 10.0 ) * 0.24} ))
  |> yield()

EDIT: How is it possible to get the first entry in the current year as range start?

1 Like
#7

Hello @matze1708,
First of all, congrats on learning so much Flux! That’s impressive. Secondly, I’m sorry for the delay. Sometimes I miss messages. Please feel free to @ me in the future.

To get the first entry in the current year as a range start you could do something like:

r = from(bucket: "my-bucket")
// -2y or long enough to capture the first point written. 
|> range(start: -2y)
|> first()
|> findRecord(fn: (key) => key.mytag == "t0", idx: 0)

timestamp = r._time

from(bucket: "my-bucket")
|> range(start: timestamp)