CODEWITHSUNDEEP
mysqlsql
Cris
Cris

Reputation: 437

MySQL query compute the timediff between the rows

I have a question regarding in querying table in mysql with conditiion.

I created a store procedure like this: 

SELECT A.*, TIMESTAMPDIFF(SECOND,A.start, COALESCE(MIN(B.start), NOW())) AS timespent_in_sec FROM activity_logs A LEFT JOIN  activity_logs B ON  B.user_id = A.user_id AND B.id > A.id WHERE A.user_id = userId AND A.start  >=   startDate  AND  A.start <= endDate GROUP BY A.id, Day(A.start) ORDER BY A.start ASC

where the userId, startDate and endDate is a parameter.

so when I execute the procedure below is the results:

id      start                   activity            user_id     timespent_in_sec
--------------------------------------------------------------------------------
1       2018-11-12 10:37:53     Login               81          124
2       2018-11-12 10:39:57     1st Break           81          59
3       2018-11-12 10:40:56     1:1 Coaching        81          35
4       2018-11-12 10:41:31     2nd Break           81          76
5       2018-11-12 10:42:47     Logout              81          63384

So my goal is to stop the computation or set it to 0 if they reach the Logout activity. Some thing like this:

id      start                   activity            user_id     timespent_in_sec
1       2018-11-12 10:37:53     Login               81          124
2       2018-11-12 10:39:57     1st Break           81          59
3       2018-11-12 10:40:56     1:1 Coaching        81          35
4       2018-11-12 10:41:31     2nd Break           81          76
5       2018-11-12 10:42:47     Logout              81          0 // means user has been logout and this will end the computation od timespent in sec

Will appreciate your help. Thanks in advance.

Upvotes: 2

Views: 38

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You can try to use CASE WHEN expression.

SELECT A.*, 
      CASE WHEN activity = 'Logout' 
           THEN 0 
           ELSE TIMESTAMPDIFF(SECOND,A.start, COALESCE(MIN(B.start), NOW())) 
       END AS timespent_in_sec
FROM activity_logs A 
LEFT JOIN  activity_logs B ON  B.user_id = A.user_id AND B.id > A.id 
WHERE A.user_id = userId AND A.start >= startDate  AND  A.start <= endDate GROUP BY A.id, Day(A.start) 
ORDER BY A.start ASC

Upvotes: 1

Related Questions