How to group data by pixel resolution

Grafana MySQL
1

Hello,

I would like to group my MySQL data by the pixel resolution of the time series panel.
You can see my problem on this example:

grafanats
grafanats2036×562 178 KB

The lines fluctuate a lot and quick over the day, resulting in a hard to read chart.
My data has a resolution of 10 seconds.
In this example there are about 80 pixel per hour, so I would need to group and avg the data by ~45seconds to get a sample point per pixel.
Can this be done somehow?

Here is my current query:

SELECT
  $__time(timestamp),
  carport_P AS `PV Carport`,
  roof_P AS `PV Dach`,
  gen_P AS `MI`,
  `battery_P` AS `Akku`,
  `grid_P` AS `Netz`,
  `load_total_P` AS `Verbrauch`,
  `battery_SOC` AS `Akku SOC`
FROM
  log.pv
WHERE
  $__timeFilter(timestamp)
ORDER BY
  timestamp DESC

Thank you very much!

2

Ok, found it out eventually.
The keyword here is $__interval_ms.
It is replaced by a number.
For example when I scale to last 2 days, I get 120000.
This is more or less the interval of a pixel. Not exactly though.
This hints that there is a way to add custom variables. I cannot make it work yet though.

SELECT
  `time`,
  CASE
    WHEN `Akku` < 0 THEN `Akku`
    ELSE 0
  END AS `Akku laden`,
  CASE
    WHEN `Akku` > 0 THEN `Akku`
    ELSE 0
  END AS `Akku entladen`,
  CASE
    WHEN `Netz` > 0 THEN `Netz`
    ELSE 0
  END AS `Einspeisung`,
  `MI`,
  `PV Carport`,
  `PV Dach`,
  CASE
    WHEN `Netz` < 0 THEN `Netz`
    ELSE 0
  END AS `Netzbezug`,
  `Verbrauch`,
  `Akku SOC`
FROM
  (
    SELECT
      ROUND(
        ROUND(
          UNIX_TIMESTAMP(`timestamp`) * 1000 / $__interval_ms
        ) * $__interval_ms
      ) AS `time`,
      AVG(gen_P) AS `MI`,
      AVG(carport_P) AS `PV Carport`,
      AVG(roof_P) AS `PV Dach`,
      AVG(`battery_P`) AS `Akku`,
      - AVG(`grid_P`) AS `Netz`,
      AVG(`load_total_P`) AS `Verbrauch`,
      AVG(`battery_SOC`) AS `Akku SOC`
    FROM
      log.pv
    WHERE
      $__timeFilter(timestamp)
    GROUP BY
      `time`
    ORDER BY
      `time` DESC
  ) AS `t`

Here is the result of grouping the values by interval.

grafanats2
grafanats21500×397 97.7 KB

3

to group and average your data by 45 seconds, you can use the following query:

SELECT
mean(carport_P) AS PV Carport,
mean(roof_P) AS PV Dach,
mean(gen_P) AS MI,
mean(battery_P) AS Akku,
mean(grid_P) AS Netz,
mean(load_total_P) AS Verbrauch,
mean(battery_SOC) AS Akku SOC
FROM
log.pv
WHERE
$__timeFilter(timestamp)
GROUP BY
time(45s)
ORDER BY
timestamp DESC

This query will group the data by 45-second intervals, and calculate the average value for each group. The resulting data points will be spaced approximately 45 seconds apart, which should result in a more readable chart.