Message-ID: <4E81F506.6010604@pinpointresearch.com>
Date: Tue, 27 Sep 2011 09:08:38 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.21) Gecko/20110831 Lightning/1.0b2 Thunderbird/3.1.13
MIME-Version: 1.0
To: Andreas <maps.on@gmx.net>
CC: pgsql-sql@postgresql.org
Subject: Re: how to calculate differences of timestamps?
References: <4E812787.1080002@gmx.net>
In-Reply-To: <4E812787.1080002@gmx.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 09/26/2011 06:31 PM, Andreas wrote:
> How could I calculate differences of timestamps in a log-table?
>
> Table log ( user_id integer, login boolean, ts timestamp )
>
> So login = true would be a login-event and login = false a logout.
> Is there a way to find the matching login/logout to calculate the 
> difference?
>
> Or is there a better table "design" to do this?
>

One way is a sub_select:

select
     o.user_id,
     o.ts as logout_time,
     (select
          max(i.ts)
      from
          log i
      where
          i.user_id = o.user_id and
          i.ts < o.ts and
          login
     ) as login_time
from
     log
where
     not login
;

This will give you login/logout time pairs. Just replace the "," with a 
"-" if you are interested in login duration.

Depending on the frequency and duration of logins and the number of 
users you may have to play with indexes though an index on ts will 
probably suffice for most cases.

Cheers,
Steve