Hello,
I have that Graph:
SELECT mean(“value”) FROM “Volumen_OelTank” WHERE $timeFilter GROUP BY time($interval) fill(null)
which shows the Volume of my tank. Now I’d like to see how much I use like every day or every week. So I like to show the difference between a value and the value one day/7days ago.
How do I have to formulate that?
I use Influxdb as input data.
Thanks for your help,
Ingo
You can do it per panel with the Graph panel timeshift override:
http://docs.grafana.org/reference/timerange/#panel-time-overrides-timeshift
But if you want to do per query then InfluxDB does not seem to currently support any timeshift functions:
https://github.com/influxdata/influxdb/issues/142 (and you can see here that lag variables have not been implemented yet)
There seems to be a temporary solution suggested here:
https://github.com/influxdata/influxdb/issues/142#issuecomment-291492226
Thanks for your reply! I will check out your suggestions the next days.
Ingo
O.K., I had a look now on you suggestions. With the first one (panel timeshift) I can get Graphs from the past, but (at least I don’t see it) can’t do math with a value from now and a value from the past. So this won’t work for me.
The thing with the time-shift-server is to complicated for me. So I will try to calculate the values within OpenHab and wait for future versions of Grafan.
Again, thanks for your answers,
Ingo
You mean future versions of InfluxDB? Time shifting on a query level is up to the database to support. Graphite and Prometheus have had it for years, for example.
What do you mean you can’t do math with a value from now a value from the past? You can have one panel with a value from now and another panel with the value from the past. Do you mean that you want to show them on the same panel? (If so then it isn’t possible with InfluxDB as discussed above).
maybe I use the wrong word “value”. I want to to calculate the usage of oil of my centralheating. The tank has a sensor which measures the volume of oil which is still in the tank. I want to take the volume from lets say “yesterday” and substract it from the volume from “now”. The difference would be the ammount I used the last day. The measured values from the tank are in the influxdb. I hope it gets a little clearer what I wanne do.
thanks
You can use influxdb difference function to get value delta over group by time interval. For example, for per day fuel usage your query can be modified to:
SELECT difference(“value”) FROM “Volumen_OelTank” WHERE $timeFilter GROUP BY time(1d) fill(null)
You can also try non_negative_derivative function. It has the advantage, that it will help handle tank refills better.
Thanks for your answer! It sounds, that’s it what I need. But it’s not working here. (I tried c&p and also edit it manually)
see picture:
Sorry if I do something wrong here, but influenza disabled me at the moment…
Also the error description in the red box ist visible completly. Is that because of my browser?
Looks like Influx expects aggregation or selection function in query.
Try
SELECT difference(last(“value”)) FROM “Volumen_OelTank” WHERE $timeFilter GROUP BY time(1d) fill(null)
or
SELECT difference(mean(“value”)) FROM “Volumen_OelTank” WHERE $timeFilter GROUP BY time(1d) fill(null)
Wow! Thanks, both are working and seem to do exactly what I want!
I will play around with it now.
Thanks yuyu!
@flensburger Even I have the same requirement.Can you tell me which grafana version are you using?
I am using Grafana v4.2.0 , any idea if I can achieve the same in this version?
i have a similar issue like the origin post, but the logic is inverse.
i have a motor for which the start and stop status is introduced in influxdb.
the start entry in the database is ON, the stop is OFF.
what i would like to do to measure the motor run time /day.
is it possible to show this in grafana somehow?
this is the query to count how many times the motor started:
"SELECT count(“Motor”) FROM “w_51” WHERE (“Motor” = ‘ON’) AND time >= 1551507788116ms and time <= 1562566988116ms GROUP BY time(1d) fill(null)
but how to measure how long was it runing?
is it possible?
thx!
I would also like to get the difference between value(now) and value(some time ago). Does anyone know if influxdb now supports that?
I have a similar problem but with two issues:
I want to see the percentage change in difference instead of the absolute value. Is there a way to do that?
I tried to do an aggregate function inside the difference() function but it returned error.
i.e. SELECT difference(last(“a”+“b”)) FROM “my_data” WHERE $timeFilter GROUP BY time(1d) fill(null)
Hi! I have a similar issue, the data that I have is in a tabular format and has 2 columns - one having the time of a previous version and another having the time of new version. I need a 3rd column having a percentage change in the time. How can it be done?
Thanks!
Iam searching for the same, does anybody know how to calculate such info ?
For IOT, its a basic info, I cannot beleive its not possibile a coorect caluclation of ON time.
I know this is late, but this is what I have done after looking at these posts:
I’m using InfluxDB with Grafana v8.1. My use case is I have the output from my energy meter that’s constantly increasing, and I just want to see my usage every hour.
For the “select” section (in the Grafana query) I have:
field(value) last() difference()
Then in “group by” I have:
time(1h)
And so far it works great!
The last() is to get the most recent for the hourly time period (my system logs every 15 seconds, so obviously only want the last value), and difference() is to get the difference between that and the previous value (I’m not sure when difference was introduced however).
For anyone that is still trying to write a query to get the difference of a value over time with InfluxDB (InfluxQL), I wrote a small blog post where I provide an example and a query based on the answers shared in this post.
Thanks @grant2 for your help!