Display the difference of two measurements from InfluxDB
How are you trying to achieve it?
I use two queries which get the last() of the two measurements grouped by 1m where values come in into InfluxDB approximately every 10 - 20s. I use a transform to substract the two values from each other.
Most of the time it actually shows the difference but sometimes either one or the other values is taken as 0 even though the gauges for these two values show something different to 0 (also using the same settings as above for the queries).
- What did you expect to happen?
I expect that I get the correct difference especially because the values are displayed correctly in the gauges.
- Can you copy/paste the configuration(s) that you are having problems with?
- Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
- Did you follow any online instructions? If so, what is the URL?
Can you share your queries and a screenshot of the transformation?
If I had to guess, the timestamps of your two queried values are not identical. If this is true, do you have a way to align them to populate Influx with the same timestamp? I do this in Node-RED when collecting the setpoint and actual values for a temperature controller.
Indeed they are not aligned but I thought that if I simply use the last value of a group of 1 minute, where I can be sure that there are values in the group, this should be fine, isn’t it?
Here are the queries:
SELECT last(“value”) FROM “total_power” WHERE $timeFilter GROUP BY time(1m) fill(null)
SELECT last(“value”) FROM “gen_power” WHERE $timeFilter GROUP BY time(1m) fill(null)
It has been a while since I used InfluxQL (this would be very easy using Flux). I think you need each query to ignore the null values. My understanding is that InfluxQL does not really have a way to do this, so the workaround is something like:
and total_power != ''
Maybe just back up a step and before working on the transformation, see if one query can generate results that contains absolutely no null or empty values.
How are the values being fed into Influx? Telegraf? Something else?
Are you married to using or heavily invested in InfluxQL, or would you be willing to move to Flux?
Hm, maybe thats more an Influx question but using last() on an interval where I have values for sure will always return a value, right? So if this is the case which actually must be because the gauges of the single values display something, the transform should only use these values and not match them by time, shouldn’t it?
Node-red puts the values to Influx.
I am actually new to Influx and Grafana and I found most tutorials using InfluxQL so it was easier for me to use that but I am open to use Flux if it easy enough to transfer InfluxQL to Flux.
OK, great…you can solve this quickly by using a join node in Node-RED. I can shoot you a sample flow or screenshot to demonstrate.
Unfortunately it is not that easy because the values come in any time so I cannot have a join node with two messages because this can lead to having two values of the same source which are different but will occur the same time then.
In that case, I would look at updating your InfluxDB to use Flux. Node-RED Influx nodes support sending data to Influx2.0 very nicely.
Thanks, I will think about it.