Multiply two field values and store the result in a new field

1

I’m logging voltage and current of electric appliances (eg. lights) in my use case, the idea being I’d like to calculate the daily energy consumption. Following are the concerns:

  1. How do I multiply voltage and current of each data point, and store it in a new field (Power)
  2. Let’s say the frequency of data collection is 5 minutes. How do I calculate the time difference (in minutes) between two successive data points? This would enable me to multiply power with time, and store the result as a new field (Energy).
  3. I want to calculate the energy consumption every day, and plot a daily profile of energy consumption. How do I segregate Date from the timestamp, and save it as a new field, so that I can reference the energy consumption by Date, and produce a plot.
2

@samdanisms What version of InfluxDB are you using? Is using Flux an option for you?

3

I’m using version 1.7.8. I’m completely a newbie to InfluxDB, so far I was trying simple queries in CLI. I’ll definitely consider Flux if it can deliver the result I’m looking for (I don’t have any prior programming experience, I’m wiling to learn though).

Do I have to use ‘User Interface’ to write Flux code? I’ve a concern in that case. Whenever I was trying to access it by going to http://localhost:8086, I get this error: ‘404 Page Not Found’. Sorry if I’m clubbing too many questions in one place.

4

Basically we’re integrating InfluxDB and Grafana in our work. We’re using InfluxDB as database source, and producing visualizations in Grafana. Is it possible to query the product of Voltage and Current directly in Grafana dashboard? How do we get to the energy from power in Grafana dashboard? Any help is highly appreciated.

5

@samdanisms Currently Flux doesn’t write data to InfluxDB 1.x targets, so it likely won’t work for your use case. About the 404 page at localhost:8086, InfluxDB 1.7.8 doesn’t ship with a built-in UI. Both Grafana and Chronograf are UI options for it.

This is definitely a problem Flux could solve, but it may also still be solvable with InfluxQL, just not as gracefully. Are voltage and current stored in the same measurement?

6

I may be a bit of a heretic here, but I typically recommend that you avoid storing a value that you can easily calculate when needed If you have the datapoints to calculate the value, they simply add a dashboard element that calculates that value in real-time. No need to store the value itself. I say this because a) storing the value doesn’t really buy you any advantage and b) storing it simply takes up space in the database that you don’t need to use.

Only collect what you need, only store what you can’t calculate from what you collect.

dg

1 Like
7

Yes, they are in the same measurement. Grafana is an appropriate tool, but the challenge is querying the product of two field values in a Grafana dashboard. I’ve tried to do that, but was unsuccessful. In the query tab, I’ve selected one field (say voltage) from the drop down list, added then a math operator (*), and then when I was selecting another field (say current), it was showing error. Any better ways of doing it? The next challenge is multiplying the power with time, so as to get the energy. Since I was getting an error, I thought probably a better way would be store the power and energy, so that we can visualize the stored values.

8

That was a great advice. I too didn’t want to save the values, but because I was not able to query the desired result in Grafana, I thought its better to store them so as to ease the visualization process. After all, my interest is in visualization of the data.

9

@samdanisms If you don’t need to store the values, the Flux will work. Grafana supports Flux: Flux (InfluxDB) [BETA] plugin for Grafana | Grafana Labs

But you also need to enable it in InfluxDB 1.7.8: Get started with Flux | Flux 0.x Documentation

I don’t know the exact structure of your data, but the Flux query would look something like this:

collectionInterval = 5.0

from(bucket: "database/retention-policy")
  |> range(start: -7d)
  |> filter(fn: (r) =>
    r._measurement == "measurement-name" and
    (r._field == "voltage" or r._field == "current")
  )
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({
    r with
    power: r.current * r.voltage,
    energy: r.current * r.voltage * collectionInterval
   }))
  |> aggregateWindow(every: 1d, column: "energy", fn: sum)
2 Likes
10

Thanks a lot Scott, I shall explore the same and share my experience here.

11

I tried to make changes in the config file (flux enabled = true; flux log enabled = true) by using sudo nano command, but no luck. Tried rebooting also, but no change were observed in the config file. @scott Can you please help me.

12

Make sure it’s under the [http] section and the setting is flux-enabled (with the dash). Save the config file and restart influxd.

[http]
  # ...
  flux-enabled = true
1 Like
13

Thank you very much @scott, its working. Came across one function called ‘Integral’, which calculates the area under curve (Power versus time). Was just thinking, isn’t it more appropriate it than sum(energy). Any thoughts?

By the way, I went through the documentation, but was not able to use ‘Integral’ in the above script. Can you please help me.

14

I believe integral() would be perfect for your use case. Since integral expects a duration for the unit parameter, you’ll need to update the collectionInterval to be a duration value. You also no longer need to compute energy in the map() function.

collectionInterval = 5m

from(bucket: "database/retention-policy")
  |> range(start: -7d)
  |> filter(fn: (r) =>
    r._measurement == "measurement-name" and
    (r._field == "voltage" or r._field == "current")
  )
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with power: r.current * r.voltage }))
  |> integral(unit: collectionInterval, column: "power")
  |> rename(column: "power", as: "energy")
  |> aggregateWindow(every: 1d, column: "energy", fn: sum)
1 Like
15

Of late I’m using Chirp Stack (CS) network server; CS, by default, stores individual values in different measurements, for instance current is stored in “r.device_frmpayload_data_LC” measurement and voltage is stored in “r.device_frmpayload_data_LV” measurement. @scott So I’ve adapted your code to my requirements, and it looks like the following:

collectionInterval = 1m

from(bucket: "chirpstack/autogen")
|> range(start: 2020-01-25T02:15:00Z)
  |> filter(fn: (r) => 
  	r._measurement == "device_frmpayload_data_LC" or r._measurement == "device_frmpayload_data_LV"
  ) 
  |> pivot(rowKey:["_time"], columnKey: ["_measurement"], valueColumn: "_value")  
  |> map(fn: (r) => ({ r with power: r.device_frmpayload_data_LC * r.device_frmpayload_data_LV }))
  |> integral(unit: collectionInterval, column: "power")
  |> rename(column: "power", as: "energy")
  |> aggregateWindow(every: 1d, column: "energy", fn: sum)

It threw an error - type error 15:6-15:43: function does not take a parameter “column”, required params

Can we use different measurements when we’re using integral function?

Also, when I compared your code with the example code in the Flux documentation

from(bucket: "telegraf/autogen")
  |> range(start: -5m)
  |> filter(fn: (r) =>
    r._measurement == "cpu" and
    r._field == "usage_system"
  )
  |> integral(unit:10s)

It may look like the following:

collectionInterval = 1m
from(bucket: "chirpstack/autogen")
|> range(start: 2020-01-25T02:15:00Z)
  |> filter(fn: (r) => 
  	r._measurement == "device_frmpayload_data_LC" or r._measurement == "device_frmpayload_data_LV"
  ) 
  |> pivot(rowKey:["_time"], columnKey: ["_measurement"], valueColumn: "_value")  
  |> map(fn: (r) => ({ r with power: r.device_frmpayload_data_LC * r.device_frmpayload_data_LV }))
  |> integral(unit: collectionInterval)

But it too threw an error - column “_value” does not exist. Do you see any modifications to be done in your code?

Any thoughts?

16

@samdanisms Your first example looks like it should work. The Flux typing system is currently undergoing a major refactor and I’m wondering if this will be resolved once the refactor is merged. I’m not sure exactly where the error is coming from since the line numbers don’t match up with the code you pasted in. Is this the full query?

For your second attempt, when you pivoted the data, it removed the _value column. By default, integral runs it’s calculation using the _value column, but you can tell it which column to use with the column parameter:

// ...
  |> integral(unit: collectionInterval, column: "power")
17

Yes it is.

This is a sort of working, but it is returning a single value for the entire period under consideration. If I were to have daily values of energy, what should I be doing? Thanks in advance.

18

@scott Would like to draw your attention please

I’ve a code as given below, which calculates a third variable from two variables (Battery Current = Load Current - Solar Current). However the code is resulting in visualization of all three variables.

collectionInterval = 1m
from(bucket: "chirpstack/autogen")
|> range(start: 2020-02-13T02:15:00Z, stop: 2020-02-28T02:15:00Z)
  |> filter(fn: (r) => (r._measurement == "device_frmpayload_data_LC" or r._measurement == "device_frmpayload_data_SC") and r.f_port == "1") 
  |> pivot(rowKey:["_time"], columnKey: ["_measurement"], valueColumn: "_value")  
  |> map(fn: (r) => ({ r with batcur: r.device_frmpayload_data_LC - r.device_frmpayload_data_SC }))

I’d like to have a visualization of the calculated variable alone i.e., battery current. Please help me out.

19

@samdanisms By using the with operator, you’re extending the row record, preserving the columns that are already there, and appending the new batcur column. Chronograf will visualize all of the “fields” in the output tables. I think all you need to do is simply drop the other field columns:

//...
  |> drop(columns: [ "device_frmpayload_data_LC", "device_frmpayload_data_SC"])
1 Like
20

This worked, thank you very much @scott.