Aggregate every minute data to daily(summed) data

Grafana MySQL
, , ,
1
  • What Grafana version and what operating system are you using?
    Grafana v10.3.1 (00a22ff8b2) - Windows 11
  • What are you trying to achieve?
    I have a database (In MySQL) having data like this:
    *|Name | TS | Value|
    |— | — | —|
    |T1000 | 2025-04-05 08:00:00 | 50|
    |T1000 | 2025-04-05 08:01:00 | 55|
    |… | … | …|
    |T1000 | 2025-04-05 09:41:00 | 70|
    | | | |
    | | | |

So every minute I receive new data for a specific Name, for example T1000, but there are many more also coming every minute. My basic approach in Grafana to plot this over a time series is using that code:
SELECT
$__timeGroup(ts,$__interval) as [time],
avg(value),
Name as Metric
FROM [myTable]with(nolock)
where $__timeFilter(ts)
group by $__timeGroup(ts,$__interval), Name
ORDER BY [time] asc

leading to that result (which is exactly what I need for a different case)

image
image1330×272 33.5 KB

  • What did you expect to happen?
    I want to have this data now shown as daily aggregated values, best shown via a bar-plot. I have looked all over the place but I can’t seem to find a working solution for that. I tried to change $__timeGroup(ts,$__interval) to
    $__timeGroup(ts, ‘1d’) but that doesn’t to it, because my x-Axis is doing strange things then.
    The solution is probably easy but I can’t find a good one ^^, maybe someone can help me.

kind regards

2

Found a solution myself.

3

SELECT
DATE(ts) as time,
avg(value) as value,
Name as metric
FROM myTable
WHERE $__timeFilter(ts)
GROUP BY DATE(ts), Name
ORDER BY time ASC

  • DATE(ts): This converts the timestamp to the date only (e.g., 2025-04-05), effectively grouping all values of a day.
  • GROUP BY DATE(ts), Name: Ensures aggregation is done per day, per metric (e.g., T1000).
  • Bar Chart Panel Settings:
  • Visualization: Choose Bar Chart.
  • X-axis: Use time as the time field (Grafana will convert it to start-of-day timestamps).
  • Display:
    • X-axis mode: Time
    • Y-axis: Avg(value)

Note : If you use $__timeGroup(ts, '1d'), Grafana may still interpret the result as a timestamp and format it strangely on the x-axis. That’s why casting with DATE(ts) is often cleaner for bar-style time grouping.

4

The OP asked for summed daily data so avg might not be what you want to propose