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

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.

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

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.

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.

@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?

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

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.

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.

@samdanisms If you don’t need to store the values, the Flux will work. Grafana supports Flux: https://grafana.com/grafana/plugins/grafana-influxdb-flux-datasource

But you also need to enable it in InfluxDB 1.7.8: https://docs.influxdata.com/flux/v0.50/introduction/installation

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(  |> 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

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

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.

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