Influxdb: Unable to take "INTEGRAL" of a math-function

[drmclean]

We store voltage and current in our influx cluster.

We can calculate power like this:

SELECT voltage*current as power FROM <db>.<rp>.<meas> WHERE <series-tag>

We want to be able to calculate energy like this:

SELECT integral(voltage * current) as energy FROM <db>.<rp>.<meas> WHERE <series-tag> and <time>

However such a query gives the followig:

ERR: SELECT integral(current * voltage) FROM <db>.<rp>.<meas> WHERE <series-tag> [panic:interface conversion: influxql.Expr is *influxql.BinaryExpr, not *influxql.VarRef]

A more standard query such as:

SELECT integral(voltage) as energy FROM <db>.<rp>.<meas> WHERE <series-tag> and <time>

works a expected.

Is it possible to achieve the result I want?
Is it possible to do math inside the new INTEGRAL function?

[yellowpattern]

The way to do this "now" would be to create a continuous query.

From https://docs.influxdata.com/influxdb/v1.3/query_language/continuous_queries/ it might be something like this:

CREATE CONTINUOUS QUERY energy ON
BEGIN
SELECT current * voltage INTO power FROM ..
END

then you can do

SELECT integral(power) ...

Not a very intuitive way to solve the problem but does it work???

[drmclean]

Unless I'm mistaken this would create ~50% more data in the database in the form of a measurement called power.

For any "one-off" style analysis we'd have to have created the CQ in advance which seems a bit inflexible, additionallly we're using the clustered version of Influx and therefore paying by the Gb so a solution that doesn't involve creating extra data that we don't strictly need would be ideal.

Is there any plan to allow mathematics inside these functions?

[yellowpattern]

Not if you then created a retention policy to limit how long the data is held for to the length of time for 1 or 2 measurement intervals.

Of course this is a lot of complexity that would be nice to not have to deal with.

[drmclean]

Even that wouldn't work as I want to be able to query the energy as well as raw current and voltage at any time so having the series continually delete itself would be unhelpful.

I think mathematics within measurements would be a neat way to solve this issue.

[jsternberg]

So I haven't fully read the issue (just skimmed it) so I apologize if this isn't helpful (and promise to read it more fully), but you can likely use subqueries for this. Part of the reason why we didn't add integral(max(value)) was because subqueries existed after we implemented integral while subqueries didn't exist when we were adding things like derivative().

If you need to do math like current * voltage, I think you can also do that with subqueries. I think something like this: SELECT integral(power) FROM (SELECT current * voltage as power FROM <db>.<rp>.<meas> WHERE <series-tag>)

[drmclean]

@jsternberg Yes this works. Perfect thank-you.