Using mathematical operators with MySQL

Hi there,

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

I hope I explained the understandable. Help would be appreciated, as every try to add the values ended in a syntax error.

Thanks :slight_smile:


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

UNIX_TIMESTAMP(date_format(time, ‘%Y-%m-%d %H:%i’)) as time_sec,
avg(value) as value,
“avg sensors” as metric
(select sensor1.time,sensor1.value from sensor1
select sensor2.time,sensor2.value from sensor2
WHERE $__timeFilter(time)
GROUP BY date_format(time, ‘%Y-%m-%d %H:%i’)

Thanks for your help!
But for some reason, this won’t work for me. I changed the table names and played around a bit, but can’t figure out the error:


I managed to get rid of the syntax error. The code is:
UNIX_TIMESTAMP(Time) as time_sec,
avg(Value) as value,
“Durchschnitt” as metric
(select Item26.Time,Item26.Value from Item26
select Item29.Time,Item29.Value from Item29
WHERE $__timeFilter(Time)

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).

if you change the query, the result it’s not the same