From: Tim Landscheidt <tim@tim-landscheidt.de>
To: pgsql-sql@postgresql.org
Subject: Re: how to calculate differences of timestamps?
Date: Tue, 27 Sep 2011 02:42:16 +0000
Message-ID: <m3k48uzoxj.fsf@passepartout.tim-landscheidt.de> (raw)
References: <4E812787.1080002@gmx.net>
(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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tim@tim-landscheidt.de
Subject: Re: how to calculate differences of timestamps?
In-Reply-To: <m3k48uzoxj.fsf@passepartout.tim-landscheidt.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox