sqltime-seriesgrafana

Display delta between last and previous in time series

The source of the query is a MySQL database, and the table is like :

Field Type
id int(11)
time timestamp
share varchar(200)
user varchar(50)
size float

The ultimate goal is to have a time series with one line per user, showing the evolution of the occupied size for a specific share (filtered by share like '%blah%'), along with the delta between the latest result and the previous one.

I have the graph displaying all of that, but I don't know how to include the delta somewhere. I don't want to plot the delta in order to keep the graph clean.

Ideally, I would like to change the display name to include the delta concatenated to the user name, but I haven’t found how to do that.

I tried to concat inside the SQL query, but it creates 2 users for each user (one without the delta, and one with it).

For now, the SQL query is like :

SELECT
  date(time) AS time,
  user,
  SUM(size) AS total_size,
  SUM(size) - LAG(SUM(size)) OVER (PARTITION BY user ORDER BY date(time) ASC) AS delta
FROM user_storage.storage
WHERE size > 150 AND share LIKE '/home%'
GROUP BY user, date(time)
ORDER BY time ASC

which returns data like :

Time delta user1 delta user2 total_size user1 total_size user2
2025-03-17 01:00:00 100 252 1589 2089
2025-03-24 01:00:00 0 100 1589 2189

Any suggestions are welcome.

Solution

  • Time Series seems to be stuck in the need for such a feature for at least 2022,
    as proven by multiple discussions even on SO.

    Some of those lead to feature request 73989 where 2 workarounds are cited:

    Failed attempts

    Exploration opened some pathways: