CODEWITHSUNDEEP
mysqlsqldatetime
user3407278
user3407278

Reputation: 1283

How to find users who are logged in but not logged out and is still logged in today in mysql?

The status 1 means Login and Status 0 means Logout. How do I find users who are logged in till now?

Here's the table 

 user_id            time                        status  

    57              2015-10-21 09:00:00         1       
    57              2015-10-21 10:00:00         0          
    60              2015-10-21 12:30:00         1          
    55              2015-10-11 08:00:00         1       
    54              2015-10-21 09:00:00         1       
    54              2015-10-21 09:15:00         0       
    50              2015-10-21 08:15:00         1       
    49              2015-10-21 11:00:00         1       
    49              2015-10-21 11:00:00         0

Expected Result : 

 user_id            status                          

    55              1       
    60              1          
    55              1          
    50              1

Upvotes: 0

Views: 264

Answers (2)

PhillipD
PhillipD

Reputation: 1817

While the query presented so far is the most straightforward on I would like to show an alternative variant:

The number of entries of a user who is not logged in at the moment is alway even (pairs of 1 and 0). If a user is logged in, the number is odd.

Consequently:

select user_id, count(*), sum(status) from tee
group by user_id having count(*)/sum(status) <> 2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271211

You want users whose more recent status is 0.

Here is one method:

select t.*
from thetable t
where t.status = 1 and
      not exists (select 1
                  from thetable t2
                  where t2.user_id = t.user_id and
                        t2.status = 0 and
                        t2.time > t.time
                 );

This can take advantage of two indexes: thetable(status, user_id) and thetable(user_id, status, time).

Upvotes: 1

Related Questions