Calculate price from kWh and variable price per hour

[huuva]

Hello

I have been googling up and down but can’t seem to find an answer for my problem so hoping someone here can help me.

I use Node-Red and InfluxDB 2.2, Node-Red send kWh and price current hour to InfluxDB.

I’m trying to calculate total kWh used every hour and how much the price is but can’t get it right.

This is the code I have and it output some numbers but they are not quite right… Any help is appreciated

import "date"
today = date.truncate(t: now(), unit: 1d)

from(bucket: "nodered")
  |> range(start: today, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "tesla")
  |> filter(fn: (r) => r["_field"] == "kWh" or r["_field"] == "price")

  |> aggregateWindow(every: 1h, fn: last, createEmpty : false, timeSrc:"_time")

  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({r with _value: r.kWh * r.price}))

My table look like this

Tried to upload the .csv file but as a new user I can’t do that yet.

0	tesla	kWh	0.0485	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:12:00.000Z
0	tesla	kWh	0.3899	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:16:00.000Z
0	tesla	kWh	0.0872	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:20:00.000Z
0	tesla	kWh	0.2992	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:24:00.000Z
0	tesla	kWh	0.7088	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:32:00.000Z
0	tesla	kWh	0.961	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:36:00.000Z
0	tesla	kWh	1.0401	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:40:00.000Z
0	tesla	kWh	1.3587	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:44:00.000Z
0	tesla	kWh	1.5698	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:48:00.000Z
0	tesla	kWh	1.7029	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:52:00.000Z
0	tesla	kWh	1.9271	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:54:09.902Z
1	tesla	price	2.9826	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:12:00.000Z
1	tesla	price	2.9826	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:16:00.000Z
1	tesla	price	2.9826	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:20:00.000Z
1	tesla	price	2.9826	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:24:00.000Z
1	tesla	price	2.9826	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:32:00.000Z
1	tesla	price	2.9826	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:36:00.000Z
1	tesla	price	2.9826	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:40:00.000Z
1	tesla	price	2.9826	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:44:00.000Z
1	tesla	price	2.9826	2022-05-04T18:54:09.902Z	2022-05-05T18:54:09.902Z	2022-05-05T18:48:00.000Z

[Anaisdg]

Hello @huuva,
It’s hard for me to say what about your current query isn’t yielding the results you expect?
It seems like you might want something like:

import "experimental/array"

kWh = from(bucket: "nodered")
  |> range(start: today, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "tesla")
  |> filter(fn: (r) => r["_field"] == "kWh")
  |> aggregateWindow(every: 1h, fn: sum, createEmpty: false)

kWh |> yield(name: "total KWh") 

price = from(bucket: "nodered")
  |> range(start: today, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "tesla")
  |> filter(fn: (r) => r["_field"] == "price")
  |> aggregateWindow(every: 1h, fn: last, createEmpty: false)

price |> yield(name: "price") 

join(
    tables: {kWh: kWh, price: price},
    on: ["_time", "_start", "_stop", "_measurement"],
    method: "inner",
)
|> map(fn:  { _value: r._value_kWh * r._value_price } )

Let me know if that helps.

[huuva]

Thanks for trying to help me

My query works for the first logged hour but after that it doesn’t add up the kWh correctly so if I used 2.3 kWh the first hour then dose 2.3 kWh gets added on to the next hour.

Your code gave me this.

error @26:25-26:26: undefined identifier r error @26:40-26:41: undefined identifier r error @26:15-26:56: expected (r:#A) => B (function) but found {_value:#C} (record) (argument fn)

[Anaisdg]

@huuva,
Whoops my apologies. Typo last line should be:

|> map(fn:  (r) => ({ r with _value: r._value_kWh * r._value_price }) )

[huuva]

Thank you so much, it didn’t give me the expected result but if I changed kWh to another value I have that calculate watt to kWh it works perfectly except for that it’s 2 hours offset from my midnight.

My price midnight is 2.2931 but the query midnight show 2.2653 but that is my 02:00 price.

I could only upload one screenshot so added all three I wanted to show you into one, hope it’s not to confusing

import "experimental/array"
import "date"
import "timezone"

today = date.truncate(t: now(), unit: 1d)
option location = timezone.fixed(offset: -1h)

kWh_watt2kWh = from(bucket: "nodered")
  |> range(start: today, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "tesla")
  |> filter(fn: (r) => r["_field"] == "kWh_watt2kWh")
  |> timeShift(duration: -1h, columns: ["_time"])
  |> aggregateWindow(every: 1h, fn: sum, createEmpty: false)

kWh_watt2kWh |> yield(name: "total kWh_watt2kWh") 

price = from(bucket: "nodered")
  |> range(start: today, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "tesla")
  |> filter(fn: (r) => r["_field"] == "price")
  |> timeShift(duration: -1h, columns: ["_time"])
  |> aggregateWindow(every: 1h, fn: last, createEmpty: false)

price |> yield(name: "price") 

join(
    tables: {kWh_watt2kWh: kWh_watt2kWh, price: price},
    on: ["_time", "_start", "_stop", "_measurement"],
    method: "inner",
)
|> map(fn:  (r) => ({ r with _value: r._value_kWh_watt2kWh * r._value_price }) )

It works if I use “-1d” instead of “today” but I want to calculate the price from my midnight since that is when the electrical company calculate their price from. Also timezone didn’t change anything.

import "experimental/array"
import "date"
import "timezone"

today = date.truncate(t: now(), unit: 1d)
option location = timezone.fixed(offset: -1h)

kWh_watt2kWh = from(bucket: "nodered")
  |> range(start: -1d, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "tesla")
  |> filter(fn: (r) => r["_field"] == "kWh_watt2kWh")
  |> timeShift(duration: 1h, columns: ["_time"])
  |> aggregateWindow(every: 1h, fn: sum, createEmpty: false)

kWh_watt2kWh |> yield(name: "total kWh_watt2kWh") 

price = from(bucket: "nodered")
  |> range(start: -1d, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "tesla")
  |> filter(fn: (r) => r["_field"] == "price")
  |> timeShift(duration: 1h, columns: ["_time"])
  |> aggregateWindow(every: 1h, fn: last, createEmpty: false)

price |> yield(name: "price") 

join(
    tables: {kWh_watt2kWh: kWh_watt2kWh, price: price},
    on: ["_time", "_start", "_stop", "_measurement"],
    method: "inner",
)
|> map(fn:  (r) => ({ r with _value: r._value_kWh_watt2kWh * r._value_price }) )

Also if it’s not to much trouble is there a way to add two other columns that sum these values so I get a running total for the day?

Thanks again for your help, really appreciate it

[huuva]

CSV file
2022-05-13_09_49_influxdb_data.gz (4.9 KB)

[huuva]

Found out how to add up the values I wanted with but I am still stuck on the timezone thing…

import "experimental/array"
import "date"
import "timezone"

today = date.truncate(t: now(), unit: 1d)
option location = timezone.fixed(offset: -1h)

kWh_watt2kWh = from(bucket: "nodered")
  |> range(start: today, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "tesla")
  |> filter(fn: (r) => r["_field"] == "kWh_watt2kWh")
  |> timeShift(duration: -1h, columns: ["_time"])
  |> aggregateWindow(every: 1h, fn: sum, createEmpty: false)

price = from(bucket: "nodered")
  |> range(start: today, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "tesla")
  |> filter(fn: (r) => r["_field"] == "price")
  |> timeShift(duration: -1h, columns: ["_time"])
  |> aggregateWindow(every: 1h, fn: last, createEmpty: false)

join(
    tables: {kWh_watt2kWh: kWh_watt2kWh, price: price},
    on: ["_time", "_start", "_stop", "_measurement"],
    method: "inner",
)
|> map(fn:  (r) => ({ r with _value: r._value_kWh_watt2kWh * r._value_price }) )

|> cumulativeSum(columns: ["_value"])
|> cumulativeSum(columns: ["_value_kWh_watt2kWh"])

[huuva]

I got it to work with your code and a few changes, probably a better way to do it but it gives me the data I want, thanks again for your help

import "experimental/array"

kWh_watt2kWh = from(bucket: "nodered")
  |> range(start: -1d)
  |> filter(fn: (r) => r["_measurement"] == "tesla")
  |> filter(fn: (r) => r["_field"] == "kWh_watt2kWh")
  |> timeShift(duration: 1h, columns: ["_time"])
  |> aggregateWindow(every: 1h, fn: sum, createEmpty: false)
  |> range(start: today())

price = from(bucket: "nodered")
  |> range(start: -1d)
  |> filter(fn: (r) => r["_measurement"] == "tesla")
  |> filter(fn: (r) => r["_field"] == "price")
  |> timeShift(duration: 1h, columns: ["_time"])
  |> aggregateWindow(every: 1h, fn: last, createEmpty: false)
  |> range(start: today())

join(
    tables: {kWh_watt2kWh: kWh_watt2kWh, price: price},
    on: ["_time", "_start", "_stop", "_measurement"],
    method: "inner",
)
|> map(fn:  (r) => ({ r with _value: r._value_kWh_watt2kWh * r._value_price }) )

|> cumulativeSum(columns: ["_value"])
|> cumulativeSum(columns: ["_value_kWh_watt2kWh"])