I am new to Grafana (and very basic with MySQL). I managed to set up my “normal” queries and graphs, but the following is a little over my head.
Goal is, to get the average temperature value form across different tables. Each table consists of two data columns, a date (e.g. 2018-11-19 16:00:00) and the temperature value. For example (dates are shortened):
Time Value
16:00 20.01
16:03 21.10
16:04 20.51
16:11 19.05
The data sources are different sensor. A new value is written every time, a sensor reports a new value. There is no synchronised update-interval. So the second table might look like:
Time Value
16:01 21.00
16:04 22.00
16:08 20.00
16:14 23.00
If possible, Grafana should generate 8 data points out of this (or seven, as 16:04 shows up twice):
Time Value
16:00 20.01 (optional, as occurring only once for each data series)
16:01 (20.01+21.00)/2
16:03 (21.01+21.00)/2
16:04 (20.51+22.00)/2
16:08 (20.51+20.00)/2
…etc.
I hope I explained the understandable. Help would be appreciated, as every try to add the values ended in a syntax error.
Hello, the following may be able to help you,
the first thing is that I suppose you have both tables in the same database,
and according to the data you provide, 8 data are generated as shown in the following image.
but it will only be one data per minute, that is, every minute the sensor data will be averaged.
this is the query
SELECT
UNIX_TIMESTAMP(date_format(time, ‘%Y-%m-%d %H:%i’)) as time_sec,
avg(value) as value,
“avg sensors” as metric
from
(select sensor1.time,sensor1.value from sensor1
union
select sensor2.time,sensor2.value from sensor2
)x
WHERE $__timeFilter(time)
GROUP BY date_format(time, ‘%Y-%m-%d %H:%i’)
ORDER BY time ASC
I managed to get rid of the syntax error. The code is:
SELECT
UNIX_TIMESTAMP(Time) as time_sec,
avg(Value) as value,
“Durchschnitt” as metric
from
(select Item26.Time,Item26.Value from Item26
union
select Item29.Time,Item29.Value from Item29
)x
WHERE $__timeFilter(Time)
GROUP BY Time
ORDER BY Time ASC
My result is same as danielgonzalez’. But this is only working, if the data of the two series has the exact same timestamp. I need a function where the last available datapoint is taken, if there is no value for the current time (or interpolate a new value from two values of one time series, but this might be overkill).
