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.
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:
Exploration opened some pathways:
total_size
pushed to the graph; however what gets in the tooltip is what the graph received, not the original value{"total_size":2189,"delta":100}
to a value of 2189 with a tooltip value of "2189 (+100)"
;total_size
from the JSON… after we've removed the delta
we want to display)select '{"size":1589}' Value, 'hey!' Text;
, or just '1589'
or 1589
)