How to set `GROUP BY time(7d)` using the selected time-range in the Dashboard? (instead of fixing it to `7d`)

Grafana Dashboards
, ,
1
  • What Grafana version and what operating system are you using?

Grafana 9.2
Ubuntu 20.04

  • What are you trying to achieve?

I have the following query, which calculates the total energy consumed in the last 7 days

SELECT SUM("difference") 
FROM (
    SELECT DIFFERENCE(MAX("aenergy_total")) AS "difference"
    FROM "pm_shelly"
    WHERE time > now() - 7d
      AND custom_id =~ /^shelly_112|shelly_113|shelly_114$/
    GROUP BY time(7d), "custom_id" 
    tz('Europe/Zurich')
) 
tz('Europe/Zurich')

This should output a pie chart like so (using additional, similar querie):

image
image640×687 32.8 KB

I would now make the time-range for the calculation configurable. I know I can use $timeFilterfor the WHERE clause like so:

SELECT SUM("difference") 
FROM (
    SELECT DIFFERENCE(MAX("aenergy_total")) AS "difference"
    FROM "pm_shelly"
    WHERE $timeFilter
      AND custom_id =~ /^shelly_112|shelly_113|shelly_114$/
    GROUP BY time(7d), "custom_id" 
    tz('Europe/Zurich')
) 
tz('Europe/Zurich')

But I do not know, how to achieve the same for the GROUP BY time(7d) part, where 7d would need to be set to the same time range.

How do I do this?

EDIT:
Come to think of it: I am not even sure that the original query does what I want. (Which is to calculate the total energy consumed over last 7 days.)
So please critique it and give me feedback on it, if you have any. Thanks!

2

please click on Query Inspector and post back the query you see there

image
image190×146 3.25 KB

3

This is the output as text from the Query Inspector:

A:1 rows

SELECT SUM("difference") 
FROM (
    SELECT DIFFERENCE(MAX("aenergy_total")) AS "difference"
    FROM "pm_shelly"
    WHERE time > now() - 7d
      AND custom_id =~ /^shelly_112|shelly_113|shelly_114$/
    GROUP BY time(7d), "custom_id" 
    tz('Europe/Zurich')
) 
tz('Europe/Zurich');SELECT SUM("difference") 
FROM (
    SELECT DIFFERENCE(MAX("aenergy_total")) AS "difference"
    FROM "pm_shelly"
    WHERE time > now() - 7d
      AND custom_id =~ /^shelly_122$/
    GROUP BY time(7d), "custom_id" 
    tz('Europe/Zurich')
) 
tz('Europe/Zurich');SELECT SUM("difference") 
FROM (
    SELECT DIFFERENCE(MAX("aenergy_total")) AS "difference"
    FROM "pm_shelly"
    WHERE time > now() - 7d
      AND custom_id =~ /^shelly_116|shelly_117|shelly_118|shelly_119|shelly_120$/
    GROUP BY time(7d), "custom_id" 
    tz('Europe/Zurich')
) 
tz('Europe/Zurich')

B:1 rows

SELECT SUM("difference") 
FROM (
    SELECT DIFFERENCE(MAX("aenergy_total")) AS "difference"
    FROM "pm_shelly"
    WHERE time > now() - 7d
      AND custom_id =~ /^shelly_112|shelly_113|shelly_114$/
    GROUP BY time(7d), "custom_id" 
    tz('Europe/Zurich')
) 
tz('Europe/Zurich');SELECT SUM("difference") 
FROM (
    SELECT DIFFERENCE(MAX("aenergy_total")) AS "difference"
    FROM "pm_shelly"
    WHERE time > now() - 7d
      AND custom_id =~ /^shelly_122$/
    GROUP BY time(7d), "custom_id" 
    tz('Europe/Zurich')
) 
tz('Europe/Zurich');SELECT SUM("difference") 
FROM (
    SELECT DIFFERENCE(MAX("aenergy_total")) AS "difference"
    FROM "pm_shelly"
    WHERE time > now() - 7d
      AND custom_id =~ /^shelly_116|shelly_117|shelly_118|shelly_119|shelly_120$/
    GROUP BY time(7d), "custom_id" 
    tz('Europe/Zurich')
) 
tz('Europe/Zurich')

Here is also a screenshot of both the Query Inspector and appearance in Query editor (below the panel). I add it, because I am confused by the output of the Query Inspector:

image
image1914×1782 210 KB

image
image3279×1674 221 KB

4

do you have a repeat option on your dashboard?

5

IMHO you don’t need GROUP BY time/timeseries at all, because used visualization doesn’t have time dimension.

6

No. The repeat option is not set on the dashboard or panel. But thanks for the pointer… I learned something! :slight_smile:

1 Like
7

Thanks for you suggestion. However, if remove the time(7d) in the GROUP BY statement, then I get this error:

Query error
InfluxDB Error: difference aggregate requires a GROUP BY interval

For completeness, this is the query without time(7d) that produces the error:

SELECT SUM("difference") 
FROM (
    SELECT DIFFERENCE(MAX("aenergy_total")) AS "difference"
    FROM "pm_shelly"
    WHERE time > now() - 7d
      AND custom_id =~ /^shelly_112|shelly_113|shelly_114$/
    GROUP BY "custom_id" 
    tz('Europe/Zurich')
) 
tz('Europe/Zurich')
8

Dig deeper. You should understand your data model. I don’t, but I’m scratching my head - why you need difference? My naive idea:

SELECT DIFFERENCE(MAX("aenergy_total")) AS "difference"
should be
SELECT SPREAD("aenergy_total") AS "difference"
and I think this is even more precise if aenergy_total is a counter.