CODEWITHSUNDEEP
mysqlsession
bspitch1
bspitch1

Reputation: 21

MySQL: Create "session" of events between two dates

Given a table with the following fields:

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

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:

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

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.

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 < logout.eventTime;

Any help would be greatly appreciated.

EDIT: The answer by @Bernd Buffen works well except when the times occur on multiple dates. For example:

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

In this event, my output table has session end times that are several days ahead. Any recommendations on how to fix this?

Upvotes: 2

Views: 111

Answers (3)

Bernd Buffen
Bernd Buffen

Reputation: 15057

Try this. I hope thats what you want

SELECT
  t.`username`,
  t.`event`,
  min(l.`time`) AS 'event_login',
  t.`time` AS 'event_time',
  max(l.`time`) AS 'event_logout'
FROM mytable t
LEFT JOIN mytable l ON t.username = l.username
GROUP BY t.username,t.event
ORDER BY `username` ASC, t.time;

Result:

+----------+---------------------+-------------+------------+--------------+
| username | event               | event_login | event_time | event_logout |
+----------+---------------------+-------------+------------+--------------+
| 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:59   | 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     |
+----------+---------------------+-------------+------------+--------------+
7 rows in set (0.00 sec)

Upvotes: 0

trincot
trincot

Reputation: 350310

You need to also make sure that if the same user has two sessions, each with their proper login and logout events, you match the right couple of login/logout events to the event you select. Note that to achieve this you must in fact maximise the time of the login event, and minimise the time of the logout event. Both should stay on the right side of the actual event, of course. So I would propose the following:

SELECT     actualEvent.username
         , actualEvent.event
         , MAX(login.eventTime) loginTime
         , actualEvent.eventTime
         , MIN(logout.eventTime) logoutTime
FROM       tableName actualEvent
INNER JOIN tableName login
       ON  actualEvent.username = login.username
       AND actualEvent.evenTime >= login.eventType
       AND login.eventType = 'login' 
INNER JOIN tableName logout
       ON  actualEvent.username = logout.username
       AND actualEvent.evenTime <= logout.eventType 
       AND logout.eventType = 'logout' 
GROUP BY   actualEvent.username
         , actualEvent.event
         , actualEvent.eventTime
ORDER BY   actualEvent.eventTime;

Upvotes: 0

Tariq
Tariq

Reputation: 2871

Supposing your table name is loggings, I think this will help (not checked):

SELECT l.username, l.event, login_time, event_time, logout_time
FROM loggings l

LEFT JOIN (
    SELECT MIN(`time`) AS login_time, username FROM loggings
    GROUP BY username
) login_tbl
ON l.username=login_tbl.username

LEFT JOIN (SELECT `time` AS event_time, username FROM loggings) event_tbl
ON l.username=event_tbl.username

LEFT JOIN (
    SELECT MAX(`time`) AS logout_time, username FROM loggings
    GROUP BY username
) AS logout_tbl
ON l.username=logout_tbl.username

Upvotes: 1

Related Questions