Skip site navigation (1) Skip section navigation (2)

Re: operating on data from multiple rows?

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: Joshua Daniel Franklin <joshua(at)iocc(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: operating on data from multiple rows?
Date: 2002-10-22 20:19:01
Message-ID: 1035317941.6372.219.camel@kant.mcmillan.net.nz (view raw or flat)
Thread:
Lists: pgsql-novice
On Wed, 2002-10-23 at 08:06, Joshua Daniel Franklin wrote:
> Here is a problem I've run into with an old IMHO poorly designed database:
> 
> There is a table ("log") that has fields 
> 
> username, sessionid, loggedtime, loggeddate, accntstatus
> 
> A SELECT might return this data, for example: 
> 
> bob	1035208	2002-10-11	11:32:00	Start
> bob	1035208	2002-10-11	11:38:00	Stop
> bob	1052072	2002-10-12	10:05:00	Start
> bob	1052072	2002-10-12	10:15:00	Stop
> 
> I'm trying to get my head around a SELECT that will return
> only one entry per sessionid with a duration instead of two entries for
> each. If I had two separate tables for Start and Stop it would
> be trivial with a join, but all I can think of is doing a 
> "SELECT ... WHERE accntstatus = 'Start'" and then grabbing the 
> sessionid and doing a separate SELECT for every record (and then the
> math to get the duration). This seems like a bad idea since thousands 
> of records are retrived at a time.
> Am I missing a better way?

Some alternatives that spring to mind:

(a) Use another table to reconstruct the data into a better form.

(b) Add a column for stop_time, or duration and maintain it separately
with a regular script.

(c) Write a function which returns the duration.

You could combine (b) and (c) as well.

Here's an example function that might give you ideas:

CREATE OR REPLACE FUNCTION duration_to ( DATE, TIME, INT ) RETURNS
TIMESPAN AS 'SELECT ($1::timestamp + $2) - (loggeddate::timestamp +
loggedtime) FROM log WHERE sessionid = $3 AND accntstatus = ''Start'' '
LANGUAGE 'SQL';

Then you should be able to do something like:

SELECT *, duration_to( loggeddate, loggedtime, sessionid FROM log WHERE
accntstatus = 'Stop';

Regards,
					Andrew.

-- 
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/         PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201     MOB: +64(21)635-694    OFFICE: +64(4)499-2267
           Survey for free with http://survey.net.nz/ 
---------------------------------------------------------------------


In response to

pgsql-novice by date

Next:From: Chad ThompsonDate: 2002-10-22 20:52:54
Subject: Error message
Previous:From: Michael PaesoldDate: 2002-10-22 19:52:52
Subject: Re: operating on data from multiple rows?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group