CODEWITHSUNDEEP
mysqlsqlmysql-5.5
Varuzhan Stepanyan
Varuzhan Stepanyan

Reputation: 97

Time difference between 2 rows in MySQL

I have the following table

TransactionID UserID TransactionDateTime
1 1 '2021-04-22 11:00:00'
2 2 '2021-04-22 11:00:11'
3 1 '2021-04-22 11:00:22'
4 3 '2021-04-22 11:00:33'
5 3 '2021-04-22 11:00:44'
6 1 '2021-04-22 11:00:55'

I want to see the time difference between transactions for each UserID. Something like this:

TransactionID UserID TransactionDateTime TimeDifference
1 1 '2021-04-22 11:00:00' NULL
2 2 '2021-04-22 11:00:11' NULL
3 1 '2021-04-22 11:00:22' 00:22
4 3 '2021-04-22 11:00:33' NULL
5 3 '2021-04-22 11:00:44' 00:11
6 1 '2021-04-22 11:00:55' 00:33

Is there any possible way to do that?

Upvotes: 0

Views: 485

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would suggest a correlated subquery to get the previous date/time in older versions of MySQL:

select t.*,
       timediff(TransactionDateTime, prev_TransactionDateTime) as timedifference
from (select t.*,
             (select max(t2.TransactionDateTime)
              from t t2
              where t2.UserId = t.UserId and
                    t2.TransactionDateTime < t.TransactionDateTime
             ) as prev_TransactionDateTime
      from t
     ) t;

Upvotes: 0

Akina
Akina

Reputation: 42632

SELECT *,
       SEC_TO_TIME(TIMESTAMPDIFF(SECOND, 
                                 TransactionDateTime,
                                 LAG(TransactionDateTime) OVER (PARTITION BY UserID
                                                                ORDER BY TransactionDateTime))) TimeDifference
FROM table
ORDER BY TransactionDateTime

but I'm using MySQL 5.5 version and the PARTITION BY function isn't supported. Maybe there is other way? – Varuzhan Stepanyan

SELECT t1.*, 
       SEC_TO_TIME(TIMESTAMPDIFF(SECOND, t2.TransactionDateTime, t1.TransactionDateTime)) TimeDifference
FROM table t1
LEFT JOIN table t2 ON t1.UserID = t2.UserID 
                 AND t1.TransactionDateTime > t2.TransactionDateTime
WHERE NOT EXISTS ( SELECT NULL
                   FROM table t3
                   WHERE t1.UserID = t3.UserID 
                     AND t1.TransactionDateTime > t3.TransactionDateTime
                     AND t3.TransactionDateTime > t2.TransactionDateTime )
ORDER BY t1.TransactionID;

https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=b7d43521afc8fe6623f152343bb88d4b

Upvotes: 1

Related Questions