I have users `connection/disconnection` log table:

```
id   user_id  timestamp         action 
1    8        2017-05-01 10:25  connect
2    8        2017-05-01 11:00  disconnect
3    8        2017-05-01 12:10  connect
3    15       2017-05-01 12:11  connect
3    8        2017-05-01 12:10  disconnect

```

**My question is who can I select user\_id:8 activity time in hours during on day e.g. `2017-05-01`?**

I have tried to select `connections` from users and `left join` users where `disconnect` time is bigger then connect time and keep first row

```
SELECT u.*,u2.* FROM `user_log` as u 
INNER JOIN  `user_log` as u2 ON u2.action = 'disconnect' 
AND u.user_id =   u2.user_id AND u.`timestamp` < u2.`timestamp`
WHERE u.action = 'connect'
GROUP BY u.id

```

Seems it working but I'm not sure if it is the best approach, also there is case which is not covered when user connected `05-01` but disconnected `05-02` so it mean I have to count till `05-01` 12:00 o'clock