Problem solve Get help with specific problems with your technologies, process and projects.

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

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:
---- -----------------
1234    90
5678	80

For More Information

  • What do you think about this answer? E-mail the edtiors at [email protected] 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.

Dig Deeper on Oracle and SQL