Sum up two queries using transformation not working, different timestamps

Hello everybody,

im running Grafana to collect power output of two different inverters using the following queries utilizing my MariaDB:

A

SELECT
UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
CAST(VALUE AS DECIMAL(10, 6)) as value,
‘Leistung Balkon’ as metric
FROM history
WHERE $__timeFilter(TIMESTAMP) AND DEVICE = ‘MQTT2_DVES_1’ AND READING = ‘ENERGY_Power’
ORDER BY TIMESTAMP ASC

B

SELECT
UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
CAST(VALUE AS DECIMAL(10, 6)) as value,
‘Leistung Piko’ as metric
FROM history
WHERE $__timeFilter(TIMESTAMP) AND DEVICE = ‘Wechselrichter_Piko’ AND READING = ‘Leistung_PV’
ORDER BY TIMESTAMP ASC

TableView

I tried to sum up the two values using the following Transform:

As you can see, the result has no value.

Summing up “Leistung Balkon + Leistung Balkon” or “Leistung Piko + Leistung Piko” is working as expected.

I know that the timestamps are quite different but having a approximate value would be fine for me. For example the sum of the nearest values as a result could work for me.

Any suggestions?

Thx in advance!

Marc

Welcome @marcde to the Grafana forums.

As you had indicated, the timestamps for the 2 fields are different, which makes mathematical operations on the two values problematic. However, you should be able to use a few successive transformations. Here is a quick mockup that I did showing two data sets that do not have the same timestamp:

To add the two columns, there is probably a nice change to your MariaDB query or some other clean method to harmonize the data, but here is a hacky way to do it in Grafana using multiple transformations:

Hi grant2!

Thx for the warm welcome!

Your hint was clearly a step in the right direction, but there is only a very little amount of values left:

Any suggestions? :pray: :upside_down_face:

Hi @marcde

Hmmm…Let’s go back to the timestamps in your MariaDB. You mentioned that you have two different inverters sending their power output to MariaDB. What method are you using to collect the data, and can you make it so the two readings come into the database with the same timestamp? It looks like you are collecting them every ~120 seconds or so?

Hi @grant2!

Both values are written into the DB by fhem, a homeautomatisation software written in Perl.
One value (Balkon) is collected by mqtt, the other one (Piko) ist genereted by httpmod every 120s.
I will try to reduce the Intervall of Piko to 60 or 30 seconds and Test again.

Synchronisation between timestamps seema difficult to me. Maybe reducing the gap will do the job.

Regards

Marc

Hi @marcde How long has the MariaDB been in use? (days or months or years of data?) Is it just these 2 readings, or many more? Would you consider switching to InfluxDB?

EDIT: Nevermind…I checked out FHEM and it appears MariaDB is the preferred database.

Hi @grant2!

DB is in use for around 2 years. There a some more readings inside, as temperature of my heating and ventilation.
DB is rather small, under 2GB. This should not be the problem.
InfluxDB would be great, but as far as i know it is not supported by fhem.
I already switched the interval to 60s and will check the Impact tomorrow.

Regards from Germany

Marc

What do you mean by above
? How different are they? Seconds,minutes,hours?

@grant2
So, i already checked. It’s better but:
Only if both data series have a value in the same second, a value is also formed in the addition for this point in time.

@yosiasz
Less than a minute.

Data series 1 (Balkon) is transmitted via MQTT at different intervals, mostly 120s.
Data series 2 (Piko) is transmitted at fixed time intervals of 60s.

@10:29:13 both series show up with a value and the addition takes place.

Is there a way to make Grafana less time sensitive?
I can live well with the values having a time offset of e.g. 15 seconds, but being added.

Calculated table after adding Transforms:

Which of 2 times is consistent? I would use that to skew the scattered numbers so as to fit the consistent number buckets

Since one is 60s every minute

Anything between 00:00:00 and 00:00:60 goes into the 1 minute bucket etc

@yosiasz

Unfortunately, neither time is fixed.
Maybe I could fix the time of the data series (Piko), but it can still deviate by 1,2,3,4 seconds. An xml file is downloaded from the inverter, parsed and the value is written to the DB. There may be some time differences…

Could Grafana maybe add the closest (in time) value of one data series to the other?

I am not sure but I wouldn’t use grafana for that
I recommend using grafana for what it does best: visualization

I would do what you want on data source

I think I may have found a way to do the calculation in fhem and then write the total value to the DB every minute. Tomorrow I will hopefully know more…

1 Like