Logic - How to calculate

Fluxlang
, ,
1

Hi all,

I struggling to get my logic in order here on how to do a calculation hope someone could provide some guidance.

Data:
I have a electricity consumption sensor that is logging from a MQTT topic into influx. It logs on change of value so frequently example data set:

time	                                                       value
2021-12-20T16:28:41.416861389Z	543.4
2021-12-20T16:28:42.500538983Z	561.73
2021-12-20T16:29:03.368917573Z	588.96
2021-12-20T16:29:12.506015646Z	570.27
2021-12-20T16:29:19.342193073Z	560.56
2021-12-20T16:29:42.511684809Z	568.24
2021-12-20T16:30:12.518064796Z	548.66
2021-12-20T16:30:38.255127611Z	620.5
2021-12-20T16:30:41.254842087Z	584.67
2021-12-20T16:30:42.25937102Z	        553.28
2021-12-20T16:30:42.545895232Z	553.28
2021-12-20T16:30:43.257105821Z	584.77
2021-12-20T16:30:50.256368831Z	551.96
2021-12-20T16:30:52.254963946Z	607.65
2021-12-20T16:30:54.254355539Z	560.69
2021-12-20T16:31:12.530639541Z	555.44
2021-12-20T16:31:42.538221108Z	573.09
2021-12-20T16:32:12.542957505Z	547.66
2021-12-20T16:32:42.546313695Z	545.18
2021-12-20T16:33:04.047610857Z	593.03
2021-12-20T16:33:06.049397424Z	546.23
2021-12-20T16:33:12.551816826Z	550.16

The value is provided in Watts.

The Question I am struggling to get my mind around:
I am visualizing this data in grafana but looking at doing the calculation at the data query layer. What I am trying to get to is :

  1. I want to get the total spend to date on this particular sensor. I thought let me do a sum of all values, gives me the total Wattage but that wont work because its not consuming that amount every time it logs, its logging the change between the last value and the current value.

So how would I break this down, 1 KW (kilowatt) is 1000 (watts) so I can calculate a kilowatt and the price of the KW (KW * Price Of a KW unit) but how would I calculate the amount spent todate based on this usage information stored?

At the end of the day I am trying to calculate since the sensor has been logging what have I paid in electricity utilization

I would appreciate any guidance - been racking my brain on this one but cant seem to nail down one path to go down?

2

Welcome to the community.
Which InfluxDB version are you using?

3

Thank you.
I am using - InfluxDB version: 1.8.10 with flux enabled.

4

Im starting to think about this and I think maybe the only way of really doing this is storing or calculating a running total?

5

What you would like to know at the end of the day is not the power in Watt but the consumed energy which is power * time (which gives you kilowatt-hour and this is what you are paying for and not kW). So you need to multiply each value by the time it is valid (meaning until the next value is coming) and then calculate the sum of each multiplication over e.g. one day.

For the first two values:
543.3W * 1.083677594s = 588.7620368202Ws (or 0.000164kWh)
561.73W * 20.86837859s = 11722.3943053607Ws (or 0.003256kWh)
… now do that for every value and calculate the sum of each over a day.

But honestly, I’m not sure this will be very accurate as your power consumption is changing more often as the values you have suggest. Does you power meter not measure the consumed energy as well?

Sorry that I cannot help with the InfluxDB specifics as I’m also pretty new here :slight_smile:

6

Well firstly THANK YOU - makes loads of sense what you saying!

So yes it does store total consumed, I was storing the actual use its real time data so as it changes it stores the new value. I am going to have to run this past some values to better get an understanding. I think I know what you saying though.

No worries on the influx side I know there are many here that could provide clever ways to calculate this via a query.

Storing the total consumed per day how would you work out that day2 - day1 then the KWH calculation?

7

If you can read also the kWh, I would definitely go with that. I have a similar setup and save that value every hour into InfluxDB. Now you can aggregate over whatever time period you would like to have.
E.g. for one day (I have an additional tag for the period):

from(bucket: "mybucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["Sensor"] == "PowerMeter")
  |> filter(fn: (r) => r["_measurement"] == "Energy")
  |> filter(fn: (r) => r["UpdateInterval"] == "1HOUR")
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false)
  |> difference()

The difference then gives you the energy consumed each day.

8

Again thank you for the time you took to respond.

So I am still running influx 1.8 been meaning to upgrade but read so many horror stories of upgrading and not getting the data into the new bucket format… anyway.

So I am using the Shelly Power devices
They don’t store the kWh (I dont think)

shellies/shellyem-<deviceid>/emeter/<i>/energy energy counter in Watt-minute
shellies/shellyem-<deviceid>/emeter/<i>/returned_energy energy returned to the grid in Watt-minute
shellies/shellyem-<deviceid>/emeter/<i>/total total energy in Wh (accumulated in device's non-volatile memory)
shellies/shellyem-<deviceid>/emeter/<i>/total_returned total energy returned to the grid in Wh (accumulated in device's non-volatile memory)
shellies/shellyem-<deviceid>/emeter/<i>/power instantaneous active power in Watts
shellies/shellyem-<deviceid>/emeter/<i>/reactive_power instantaneous reactive power in Watts
shellies/shellyem-<deviceid>/emeter/<i>/voltage grid voltage in Volts

I am currently storing:

shellies/shellyem-<deviceid>/emeter/<i>/energy
AND
shellies/shellyem-<deviceid>/emeter/<i>/total

I would still need to calculate the kWh from that.

9

Of course they store the total consumed energy, that’s this one:
shellies/shellyem-<deviceid>/emeter/<i>/total
It’s in Wh but that’s just the factor 1000 to k(ilo)Wh. So I would store this one and then make the query as above.
I did the upgrade from 1.7 and had absolutely no problems and no data loss. But did it before reading any forum posts :joy:

10

very interesting converstion, I have a similar question.

I’m using InfluxDB2 on Ubuntu and its reading data from an off-grid solar system via bluetooth using MQTT from a RPi Zero 2W via red Node (Ubuntu mqtt Broker). Here’s the Grafana dashboard on Ubuntu showing the data coming in:

One the units is watts generated by my solar panels. I’d like to be able to calculate power generated (not consumed) via the watt data, and as you know:

kWh = (watts × hrs) ÷ 1,000

How can I create a new panel plot which shows accumulated kWh production from the watt influx? There is no stored accumulated data (that I know of).

11

Sorry, can’t really help here. This here seems to be the same problem:

Maybe you can translate this solution to the Flux language.

1 Like