Grafana unable to add up total due to different timestamps

InfluxDB 1
1

Hey guys

First time on this forum here and I have been a user of InfluxDB + Grafana to monitor my KNX equipped home and my energy flows.

I have 2 solar plants on my property and I want to display them in Grafana. Grafana can take 2 values and add them as a separate total.

However, it is apparently unable sometimes to add the 2 values because (what I assume) they do not carry the same timestamp. I thought I could solve this by grouping them by 1s but due to rounding and when one of the 2 solar plants is already shutting down due to shades the interval of the logged values is more than 1 second apart.

Is there any way to override the value of the timestamp so that they always align?

image
image1661×119 8.37 KB 

select last(vermogen)
FROM (
SELECT mean("number_SMA_ACVermogen") as vermogen
FROM "SMA_values" 
WHERE $timeFilter
AND ("host" = 'SMA1')
GROUP BY time(1s)
)
2

if feasible (I don’t have the whole scope of your data so it’s hard to tell) you can manipulate the data using Grafana itself and its transformation…

In this case, you could use “add field from calculation” and keep only the last non-null value…
It will work with just those two rows, if you have more it won’t really help…

here are some general suggestions:

  • have a good look at GrafanaTransformations, they can help solve lots of problems
  • you can try to use the fill() function, it won’t solve all the problems but some rows will be usable
  • workaround the issue by aggregating on wider periods or by using different calculations. But this is up to a lot of factors (ie: gathering frequency, kind of data cumulative/point, desired results, etc)
3

Ciao Gio
thanks for your response.
Below some detailed feedback, but quick question: can’t I format the time/date as YYYY-MM-DD HH:MM without the seconds or something like that?

Option 3:
I have been looking at the aggregating range indeed, but as you said, it’s up to a lot of factors, the dashboard I want to build should serve as a RIGHT NOW current values, because I want/need to be able to look at current flows right at that time, not over “the last X s or Y min”.

Option 2:
I will have to look into that

Option 1:
I have tried it but it bumps into some errors there too.

4

if you actually need just the latest value you can:

  • use the Grafana transformation to get the “Last non-blank”, but you will lose the timestamp.
  • use last() instead of mean() (if both are needed use a subquery and paly a bit with time ranges), sample below
SELECT last(MyValue1), last(MyValue2) FROM ___
WHERE time > now()-10s

You don’t even need the time grouping if you need the latest value over the whole range

5

Hi, maybe I am missing something but when doing that I get the same results, being 2 values not aligned on the timestamp.

image
image1898×123 8.75 KB

How do I manipulate the timestamp so that it can combine the two values?

6

image

7

you also need to group by time, in order to have a “fixed” timestamp (in this case time(1m) ) or use some grafana transformation to merge the data anyway.

8

After my last post and in the meantime I found it out, indeed.
For anyone looking for the same, here’s the final solution:

image
image1914×834 60.3 KB

9

You don’t need a subquery to do that, the following will work

SELECT
	last(_value_) as _ValueAlias_
FROM _Measurement_
WHERE
	time > now()-1m
	AND ...
GROUP BY
	time(1m)

The group by time interval must be equal or higher than the time filter you apply ofc