Calculate interval in timestamps by groups-CodePudding

I have this table with My Id, "rank" the order messages where sent, and the message_send_time.

ID      Rank          message_send_time      
1         1          2022-01-01 00:33:04              
1         2          2022-01-01 00:34:04            
2         1          2022-01-01 00:30:04        
2         2          2022-01-01 00:32:04            
2         3          2022-01-01 00:33:04

I want to calculate the interval of minutes between my group Id and based on the rank oh the messages, how I can calculate this in SQL ?

ID      Rank          message_send_time       Interval_time_minutes
1         1          2022-01-01 00:33:04              
1         2          2022-01-01 00:34:04            1
2         1          2022-01-01 00:30:04        
2         2          2022-01-01 00:32:04            2
2         3          2022-01-01 00:33:04            1

CodePudding user response：

You can try to use lag window function with TIMESTAMPDIFF

Query #1

select
  id, 
  `Rank`,
  message_send_time,
  TIMESTAMPDIFF(MINUTE,lag(message_send_time,1) over (partition by id order by `Rank`),message_send_time) Interval_time_minutes
from T;

id	Rank	message_send_time	Interval_time_minutes
1	1	2022-01-01 00:33:04
1	2	2022-01-01 00:34:04	1
2	1	2022-01-01 00:30:04
2	2	2022-01-01 00:32:04	2
2	3	2022-01-01 00:33:04	1

View on DB Fiddle

CodePudding user response：

We can use the functions datediff() and lag()

create table messages(
ID int,
message_send_time timestamp);
insert into messages values
(1,' 2022-01-01 00:33:04'),
(1,' 2022-01-01 00:34:04'),
(2,' 2022-01-01 00:30:04'),
(2,' 2022-01-01 00:32:04'), 
(2,' 2022-01-01 00:33:04');

select
  id, 
  rank() over(partition by id order by message_send_time) "Rank",
  message_send_time,
  timediff(
    message_send_time,
    lag(message_send_time,1) over (partition by id order by message_send_time) 
     ) as "Interval"
from messages;

id | Rank | message_send_time   | Interval
-: | ---: | :------------------ | :-------
 1 |    1 | 2022-01-01 00:33:04 | null    
 1 |    2 | 2022-01-01 00:34:04 | 00:01:00
 2 |    1 | 2022-01-01 00:30:04 | null    
 2 |    2 | 2022-01-01 00:32:04 | 00:02:00
 2 |    3 | 2022-01-01 00:33:04 | 00:01:00

db<>fiddle here

CodePudding user response：

select
  id 
, `Rank`
, message_send_time,
  TIMESTAMPDIFF(
     MINUTE
   , LAG(message_send_time,1) over (
       partition by id order by `Rank`
     )
   , message_send_time
   ) Interval_time_minutes
from T;