Time between two events

Question

If have got a table that tracks some events of a user.

 id | user_id | action |         created_at         
----+---------+--------+----------------------------
  5 |       1 | create | 2016-09-08 11:29:56.325691
  6 |       1 | clear  | 2016-09-08 11:30:00.08604
  7 |       2 | create | 2016-09-08 11:30:10.12857
  8 |       2 | clear  | 2016-09-08 11:30:14.238685
  9 |       3 | create | 2016-09-08 11:30:42.192843

There is always a create action that might be followed by a clear action.

CREATE TYPE user_actions AS ENUM ('create', 'clear');

Now I want to query the time difference between these two actions to get the time_diff between the create and clear of a user.

For now you can assume that the user doesn't have multiple entries (e.g. at least one create and maximal another clear).

I would like to have a result like this:

 user_id |    time_diff    
---------+-----------------
       1 | 00:00:03.760349
       2 | 00:00:04.110115

Answer 1

You can use the following query:

SELECT user_id,
       MAX(CASE WHEN action = 'clear' THEN created_at END) -
       MAX(CASE WHEN action = 'create' THEN created_at END) AS time_diff 
FROM mytable
GROUP BY user_id 
HAVING COUNT(*) = 2

The HAVING clause filters out user_id groups containing only a create action, like record with id=9 in the OP.

Demo here