Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 72F539FBCC6 for ; Wed, 13 Jun 2007 13:52:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 30683-02 for ; Wed, 13 Jun 2007 13:51:58 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 6F6639FBB92 for ; Wed, 13 Jun 2007 13:51:58 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 76F2F3144C; Wed, 13 Jun 2007 18:51:56 +0200 (MET DST) From: =?iso-8859-1?q?Rodrigo_De_Le=F3n?= X-Newsgroups: pgsql.sql Subject: Re: calculate time diffs across rows with single timestamp Date: Wed, 13 Jun 2007 16:51:55 -0000 Organization: http://groups.google.com Lines: 21 Message-ID: <1181753515.419178.63440@j4g2000prf.googlegroups.com> References: <467018B0.30108@ibss.net> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Complaints-To: groups-abuse@google.com In-Reply-To: <467018B0.30108@ibss.net> User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.4) Gecko/20070515 Firefox/2.0.0.4,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: j4g2000prf.googlegroups.com; posting-host=200.75.231.230; posting-account=zGrw3Q0AAADU1m7dOiE5S6vTNtvJ7FdA To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/94 X-Sequence-Number: 28584 On Jun 13, 11:17 am, bsingle...@ibss.net (Bob Singleton) wrote: > Any suggestions on how I can build a result set that would return > > ASSET 001 AAA 1:00 (1 hour) > ASSET 001 BBB 0:10 (10 minutes) > ASSET 001 CCC 0:08 (8 minutes) > ASSET 001 DDD {difference between timestamp and now()} > ASSET 002 AAA 0:03 (3 minutes) > ASSET 002 BBB {difference detween timestamp and now()} > ASSET 003 AAA{diff between timestamp and now()} SELECT TYPE, ID, STATUS , (COALESCE( (SELECT MIN(DATETIME) FROM STATUSLOG WHERE TYPE = S.TYPE AND ID = S.ID AND DATETIME > S.DATETIME) , NOW()::TIMESTAMP ) - DATETIME) AS DURATION FROM STATUSLOG S ORDER BY TYPE, ID, STATUS