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
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.
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:
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?
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.
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.
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.
@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.
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.
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 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…
In the meantime, I ran across the truncateTimeColumn() function (which I never knew existed). It is designed to normalize irregular timestamps. This function truncates all _time values to a specified unit (e.g. to the minute).
