CODEWITHSUNDEEP
mysqlsql
eager_learner313
eager_learner313

Reputation: 97

MySQL get time duration up to a selected action

I am have the following sequence of actions for users and a DateTimetime stamp of each action. Below is an example table

Actions:
+------------+-------------+----------------------+
| session_id | action_name | time                 | 
+------------+-------------+----------------------+
| 123abcd    | ADD         | 2014-08-27 13:41:02  |
+------------+-------------+----------------------+
| 123abcd    | LIKE        | 2014-08-27 13:43:02  |
+------------+-------------+----------------------+
| 123abcd    | DELETE      | 2014-08-27 13:45:02  |
+------------+-------------+----------------------+
| 123abcd    | EMAIL       | 2014-08-27 13:48:02  |
+------------+-------------+----------------------+
| 123abcd    | LIKE        | 2014-08-27 13:52:02  |
+------------+-------------+----------------------+
| 546erqr    | ADD         | 2014-08-27 14:02:02  |
+------------+-------------+----------------------+
| 546erqr    | EMAIL       | 2014-08-27 14:12:02  |
+------------+-------------+----------------------+
| 546erqr    | LIKE        | 2014-08-27 14:14:02  |
+------------+-------------+----------------------+

As you can see a user can go through a session performing certain actions.

I am interested in calculating the time spent before the user sends the email for each session.

Essentially, I want to do a TIMESTAMPDIFF(SECOND, min(time), max(time))/60 till the action = EMAIL. I tried executing it using a where clause in the SQL statement, but it didn't work.

For the output, I want something like:

+------------+-----------------------+
| session_id | time_till_email_mins  | 
+------------+-----------------------+
| 123abcd    | 7                     |
+------------+-----------------------+
| 546erqr    | 10                    |
+------------+-----------------------+

In general, if one were to do computations like this (be it time difference calculations or counting something) till a certain action, how does one go about it.

Hope this makes sense. Please let me know if you have any questions

Upvotes: 0

Views: 85

Answers (3)

Brian DeMilia
Brian DeMilia

Reputation: 13248

I think you probably want this:

select   session_id,
         min(time) as first_action_time,
         min(case when action_name = 'EMAIL' then time end) as first_email_time,
         timestampdiff(second,min(time),min(case when action_name = 'EMAIL' then time end)) as diff_in_secs
from     actions
group by session_id

This is the difference in seconds between the first action for the session and the first action for the session where the action name is 'EMAIL'.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can do this with conditional aggregation:

select session_id,
       timestampdiff(second, min(case when action_name = 'EMAIL' then action_time end),
                     min(action_time)) / 60
from actions a
group by session_id;

Upvotes: 1

Twelfth
Twelfth

Reputation: 7180

Use a subquery to obtain both values. Time isn't really a good column name.

select session_ID , **(datediff function using first_time and end_time)**

from
(select session_id , min(time) as first_time 
from actions 
group by session)a

inner join

(select session_id, time as end_time
from actions
where action_name = 'email'
group by session_ID) b

on a.session_id = b.session_id

Unfortunately I'm not familiar with the datediff/time diff functions in MySQL (I put a placeholder) in the select line for that), but you should be able to work it from here.

Upvotes: 0

Related Questions