Обсуждение: how to calculate differences of timestamps?

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?

(anonymous) 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?

This is similar to the question Dianna asked some time ago:

| SELECT user_id,
|        prev_ts AS login_ts,
|        ts AS logout_ts
|        FROM (SELECT user_id,
|                     LAG(login) OVER (PARTITION BY user_id ORDER BY ts) AS prev_login,
|                     LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) AS prev_ts,
|                     login,
|                     ts FROM log) AS SubQuery
|        WHERE prev_login AND NOT login;

> Or is there a better table "design" to do this?

That depends on your requirements and your application de-
sign. The query above requires a full table scan which may
kill performance in some circumstances.
 Of course, any design has to deal with the possibility of
an event not having been logged, multiple logins, etc. The
query above just forms pairs based on temporal proximity.

Tim

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