Ask the Expert

Displaying time ranges based on login, logout

I have a table that lists users who have logged in and out of a server. The table lists the PID, which is a number for their ID, an event -- either logged in or logged out, and a timestamp. I threw in a small sample table below... How do I write an SQL statment to display the PID and time the user was logged in to the server?
PID	Event	TimeStamp
1234	Login	10:00
5678	Login	10:20
1234	Logout	11:30
5678	Logout	11:40

    Requires Free Membership to View

The trick here is to match up each login with its logout. Again, a scalar subquery in the SELECT clause comes to the rescue. But first, let's create and populate our table (note: this is Oracle flavored SQL):

create table ConnectionLog
( PID number(4),
  Event varchar(6),
  Logged date
);

insert into ConnectionLog values (1234, 'Login',
  to_date('12-MAR-2002 10:00','DD-MON-YYYY HH24:MI'));
insert into ConnectionLog values (5678, 'Login', 
  to_date('12-MAR-2002 10:20','DD-MON-YYYY HH24:MI'));
insert into ConnectionLog values (1234, 'Logout',
  to_date('12-MAR-2002 11:30','DD-MON-YYYY HH24:MI'));
insert into ConnectionLog values (5678, 'Logout', 
  to_date('12-MAR-2002 11:40','DD-MON-YYYY HH24:MI'));
Our scalar subquery will return the earliest logout that occurs after the login for each PID. Subtract the login from the logout and we have the duration. Here's the SQL:
select PID, ( ( select min(Logged)
                from ConnectionLog
                where PID = t.PID
                  and Logged > t.Logged )
              - Logged 
            ) * 24 * 60 as DurationInMinutes
  from ConnectionLog t
  where Event = 'Login';
Notice I didn't specify that Event must be 'Logout' in the subquery. This is by design. If the session doesn't logout before a subsequent login, the subquery will act as though there is an implicit logout. That is, the next event, whether it is a login or a logout, is treated as a logout for the purpose of the subquery. Here are the results of the query:
PID  DURATIONINMINUTES
---- -----------------
1234    90
5678	80

For More Information

  • What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was first published in March 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: