title: How to get timespans between entities from timed log table?
body: <p>I have a log table to store user's login/logout logs. My goal is to calculate how many times each user logged in and logined-times for each login.</p>
  
  <p>I'm working on PostgreSQL database. Log table has log_id(PK), user_id(FK), login_state, created_time. login_state column is enum type and its value is either 'login' or 'logout'.</p>
  
  <p>For now, I used self join on Log table like below.</p>
  
  <pre><code>SELECT A.log_id, A.user_id, A.login_state, A.created_time, B.log_id, B.login_state, B.created_time, (B.created_time-A.created_time) elapsedtime 
  FROM logtable A INNER JOIN logtable B ON (A.login_state='login' AND B.login_state='logout') 
  WHERE (A.user_id=B.user_id) AND (A.created_time&lt;=B.created_time);
  </code></pre>
  
  <p>I got some right records but there are also wrong records. </p>
  
  <p>I think maybe join couldn't be a solution. For each login entity, only one logout entity should be matched but I couldn't write the right query statement for this.</p>
  
  <p>The best result could be a collection of login-logout pairs and it's elapsed time for each user.</p>
  
  <p>Need some helps. Thanks.</p>
  
  <p>============== Add some sample data and expected results =========</p>
  
  <p>Sample Log Table</p>
  
  <p><a href="https://i.stack.imgur.com/ndRxl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ndRxl.png" alt="Sample Log Table"></a></p>
  
  <p>Expect Results
  <a href="https://i.stack.imgur.com/n5WPA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/n5WPA.png" alt="Expect Results"></a></p>
  
  <p>DB Fiddler for test
  <a href="https://www.db-fiddle.com/f/vz6EyKKTg6PWs1X4HbTspB/0" rel="nofollow noreferrer">https://www.db-fiddle.com/f/vz6EyKKTg6PWs1X4HbTspB/0</a></p>