title: MySQL: Create "session" of events between two dates
body: <p>Given a table with the following fields:</p>
  
  <pre><code>username |  event                |  time (hh:mm:ss)     
   user2   |   login               |   03:27:17
   user2   |   check messages      |   03:31:31
   user2   |   view profile        |   03:32:01
   user2   |   logout              |   03:32:48
   user3   |   login               |   13:00:59
   user3   |   change billing info |   13:03:11
   user3   |   logout              |   13:03:32
  </code></pre>
  
  <p>I am trying to create user "sessions", using the first time as the "login" time and the last time as the "logout" time. The resulting table should be:</p>
  
  <pre><code>username |  event                |  login time  |  event time  |  logout time
   user2   |   login               |   03:27:17   |   03:27:17   |   03:32:48
   user2   |   check messages      |   03:27:17   |   03:31:31   |   03:32:48
   user2   |   view profile        |   03:27:17   |   03:32:01   |   03:32:48
   user2   |   logout              |   03:27:17   |   03:32:48   |   03:32:48
   user3   |   login               |   13:00:59   |   13:00:39   |   13:03:32
   user3   |   change billing info |   13:00:59   |   13:03:11   |   13:03:32
   user3   |   logout              |   13:00:59   |   13:03:32   |   13:03:32
  </code></pre>
  
  <p>I have tried to use the min(time) and max(time) to get the login and logout times but when I do, I get login, event, and logout times that are mixed.</p>
  
  <pre><code>SELECT login.eventTime, actualEvent.eventTime, logout.eventTime
  FROM tableName login, tableName actualEvent, tableName logout
  WHERE login.username = actualEvent.username
  AND actualEvent.username = logout.username
  AND login.eventTime = 
     (SELECT MIN(minTime.eventTime)
     FROM tableName minTime
     WHERE minTime.username = login.username)
  AND logout.eventTime = 
     (SELECT MAX(maxTime.eventTime)
     FROM tableName maxTime
     WHERE maxTime.username = login.username)
  AND login.eventTime &lt; logout.eventTime;
  </code></pre>
  
  <p>Any help would be greatly appreciated.</p>
  
  <p>EDIT:
  The answer by @Bernd Buffen works well except when the times occur on multiple dates. For example:</p>
  
  <pre><code>username |  event                |  time (hh:mm:ss)     
   user2   |   login               |   08/11/2015 03:27:17
   user2   |   check messages      |   08/11/2015 03:31:31
   user2   |   view profile        |   08/11/2015 03:32:01
   user2   |   logout              |   08/11/2015 03:32:48
   user3   |   login               |   08/11/2015 13:00:59
   user3   |   change billing info |   08/11/2015 13:03:11
   user3   |   logout              |   08/11/2015 13:03:32
   user2   |   login               |   08/12/2015 04:00:00
   user2   |   change billing info |   08/12/2015 04:03:22
   user2   |   logout              |   08/12/2015 04:08:17
  </code></pre>
  
  <p>In this event, my output table has session end times that are several days ahead. Any recommendations on how to fix this?</p>