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 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 last published in March 2002

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.