Display delta between last and previous in time series

Grafana Time Series Panel
1

Hi,

  • What Grafana version and what operating system are you using? 11.5.2 in Docker

  • What are you trying to achieve?
    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.

  • How are you trying to achieve it?
    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.
    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

I also tried to cast the delta into a char(10), but it creates a new line for each user/delta couple.

I tried to hide the delta into the graph and edit the “Display name” to add this information inside the legend, but I did not find the right variable to display an other SQL field here.

The SQL table is like :

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

Previous SQL query returns :

Time delta user1 delta user2 total_size user1 total_size user2
2025-03-10 01:00:00 0 245 520 494
2025-03-17 01:00:00 18.8 438 539 932
2025-03-24 01:00:00 546 163 1085 1095

As you can see, for the user2 the last delta is equal to the last total_size - pervious total_size (1095 - 932).

Thanks for any suggestions.

2

This is more of sql question than a grafana question but that said

what is your datasource? mysql or MSSQL?

also please provide your data as proper DDL an DML

create table #storage
(
id int(11),
time timestamp,
share varchar(200),
user varchar(50),
size float
)

insert into #storage
select 1, '2025-03-10 01:00:00', 'food','user1', 245 
--etc
3

It’s a MySQL and here is the DDL/DML (just a sample of course) :

CREATE TABLE `storage` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `initiator` varchar(50) DEFAULT NULL,
  `server` varchar(50) DEFAULT NULL,
  `share` varchar(200) DEFAULT NULL,
  `user` varchar(50) DEFAULT NULL,
  `size` float DEFAULT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO `storage` VALUES (17696,'2025-03-09 23:00:00','my_source_ip','my_storage_server','/home/user1','user1',678.636);
INSERT INTO `storage` VALUES (18252,'2025-03-09 23:00:00','my_source_ip','my_storage_server','/home/user2','user2',520.458);
INSERT INTO `storage` VALUES (18517,'2025-03-16 23:00:00','my_source_ip','my_storage_server','/home/user1','user1',1093.45);
INSERT INTO `storage` VALUES (19051,'2025-03-16 23:00:00','my_source_ip','my_storage_server','/home/user2','user2',539.24);
INSERT INTO `storage` VALUES (19494,'2025-03-23 23:00:00','my_source_ip','my_storage_server','/home/user1','user1',29.0984);
INSERT INTO `storage` VALUES (20077,'2025-03-23 23:00:00','my_source_ip','my_storage_server','/home/user2','user2',1084.91);

Thanks

1 Like