SQL command does not execute in grafana

Grafana MySQL
1

Hy

I tried to plot data in grafana with an SQL command that works on my HeidiSQL program.

SET @startDate = '2022-07-31T22:00:00.000Z';
SET @endDate = '2022-08-24T09:25:26.532Z';

SELECT DATE_FORMAT(temp.time, '%Y-%m-%d') as DAY, COUNT(temp.value), SUM(temp.value) / (60 / (TIME_TO_SEC(TIMEDIFF(MAX(temp.time), MIN(temp.time))) / COUNT(temp.value)) * 60) / 1000 as kWhPV
FROM	openhab.evcc_pvpower_0451 temp,
		(SELECT openhab.evcc_pvpower_0451.time FROM openhab.evcc_pvpower_0451 WHERE openhab.evcc_pvpower_0451.time > @startDate AND openhab.evcc_pvpower_0451.time <= @endDate GROUP BY DATE_FORMAT(openhab.evcc_pvpower_0451.time, '%Y%m%d') ORDER BY openhab.evcc_pvpower_0451.time DESC LIMIT 1 ) x,
		(SELECT openhab.evcc_pvpower_0451.time FROM openhab.evcc_pvpower_0451 WHERE openhab.evcc_pvpower_0451.time > @startDate AND openhab.evcc_pvpower_0451.time <= @endDate GROUP BY DATE_FORMAT(openhab.evcc_pvpower_0451.time, '%Y%m%d') ORDER BY openhab.evcc_pvpower_0451.time ASC  LIMIT 1) y
WHERE temp.time > @startDate AND temp.time <= @endDate
GROUP BY
  DATE_FORMAT(temp.time, '%Y%m%d')

When I rund this command in a Bar Chart Grafana returns:

db query error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SET @endDate = ‘2022-08-24T09:25:26.532Z’; SELECT DATE_FORMAT(temp.time, ‘…’ at line 2

@startDate and SET @endDate are set via:
‘${__from:date}’
and
‘${__to:date}’

But the abvoe SQL comannd is taken from the Querry Inspector so why is Grafana not able to querry the Command where HeidiSQL has no problems with it?

2

Hello,
i believe you can’t use “set” statement,
same as update, insert and other modification statement .

try without the set statement and you should be able to run the query

3

thx a lot didn’t know that