MySQL to Grafana

Grafana Time Series Panel
1

Hello!

I have a SQL and it works fine in PHPMyAdmin:

SELECT 
    AVG(Energy) AS "AVG"
    FROM (
        SELECT
          UNIX_TIMESTAMP(time) DIV 86400 * 86400 AS "time",
          count(val)/1000 AS "Energy"
        FROM history
        WHERE
          time BETWEEN FROM_UNIXTIME(1643380305) AND FROM_UNIXTIME(1645972305) AND
          topic = 'hesi/home/energy'
        GROUP BY 1
	) AS xx

And the inner Select works fine in Grafana:

SELECT
  $__timeGroupAlias(time,24h),
  count(val)/1000 AS "Energy"
FROM history
WHERE
  $__timeFilter(time) AND
  topic = 'hesi/home/energy'
GROUP BY 1
ORDER BY $__timeGroup(time,1h)

But as soon as I add the outer Select I won’t get any data.

I have a bar chart with energy consumption of my house per day(inner Select) and I wanna add a line with the average energy consumption per day(outer Select).
Can someone point me in the right direction or show me how to convert SQL to Grafana SQL?

Best regards
Alex

2

There is no such thing as “Grafana SQL” - whatever SQL you enter into Grafana will be sent to your datasource/DB. I believe your problem is that for a normal (for example bar chart) in Grafana you would select three things:

  1. A date/time.
  2. A Metric (e.g. the title of the 1 or more bars you want to show).
  3. A Value.

At the very least you need a time and a value which is what your inner select has so it works. Your other AVG select is only selecting a single value, no date, no metric so Grafana cannot show a graph with just a single value.

3

OK thanks for the reply!
I didn’t had a date/time in my query, thanks!

This is the my Query now:

SELECT
    $__time(),
    AVG(Energy) AS "AVG"
    FROM (
        SELECT
          $__timeGroupAlias(time,24h),
          count(val)/1000 AS "Energy"
        FROM history
        WHERE
          topic = 'hesi/home/energy'
        GROUP BY 1
	) AS xx

“$__time()” gives me the current time and “AVG(Energy)” the average energy per day.

I thought I could just make it a horizontal line with some options, but I can’t find any.
Is there any trick to make it a horizontal line? I found this one, but I don’t get it…