Mail-Followup-To: pgsql-sql@postgresql.org
To: pgsql-sql@postgresql.org
From: Tim Landscheidt <tim@tim-landscheidt.de>
Subject: Re: how to calculate differences of timestamps?
Date: Tue, 27 Sep 2011 02:42:16 +0000
Organization: <URI:http://www.tim-landscheidt.de/>
Lines: 33
Message-ID: <m3k48uzoxj.fsf@passepartout.tim-landscheidt.de>
References: <4E812787.1080002@gmx.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Mail-Copies-To: never
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:zJzVMlu81PCU91jDOCaMghlbHWo=

(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