pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to calculate differences of timestamps?
3+ messages / 3 participants
[nested] [flat]
* how to calculate differences of timestamps?
@ 2011-09-27 01:31 Andreas <maps.on@gmx.net>
0 siblings, 2 replies; 3+ messages in thread
From: Andreas @ 2011-09-27 01:31 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to calculate differences of timestamps?
@ 2011-09-27 02:42 Tim Landscheidt <tim@tim-landscheidt.de>
parent: Andreas <maps.on@gmx.net>
1 sibling, 0 replies; 3+ messages in thread
From: Tim Landscheidt @ 2011-09-27 02:42 UTC (permalink / raw)
To: pgsql-sql
(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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to calculate differences of timestamps?
@ 2011-09-27 16:08 Steve Crawford <scrawford@pinpointresearch.com>
parent: Andreas <maps.on@gmx.net>
1 sibling, 0 replies; 3+ messages in thread
From: Steve Crawford @ 2011-09-27 16:08 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-09-27 16:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-27 01:31 how to calculate differences of timestamps? Andreas <maps.on@gmx.net>
2011-09-27 02:42 ` Tim Landscheidt <tim@tim-landscheidt.de>
2011-09-27 16:08 ` Steve Crawford <scrawford@pinpointresearch.com>
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