Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 16AC92E2D75 for ; Wed, 12 Dec 2007 16:21:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 46605-04 for ; Wed, 12 Dec 2007 16:21:12 -0400 (AST) Received: from fhw-relay07.plus.net (fhw-relay07.plus.net [212.159.14.215]) by postgresql.org (Postfix) with ESMTP id 811612E35EE for ; Wed, 12 Dec 2007 16:21:24 -0400 (AST) Received: from [84.51.143.99] (helo=server3.office.archonet.com) by fhw-relay07.plus.net with esmtp (Exim) id 1J2Y54-0006wI-Ec; Wed, 12 Dec 2007 20:21:22 +0000 Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id 2304527404B; Wed, 12 Dec 2007 20:21:22 +0000 (GMT) Message-ID: <476042C1.8070707@archonet.com> Date: Wed, 12 Dec 2007 20:21:21 +0000 From: Richard Huxton User-Agent: Thunderbird 2.0.0.9 (X11/20071031) MIME-Version: 1.0 To: Gary Chambers CC: pgsql-sql@postgresql.org Subject: Re: Query Assistance References: <302670f20712120858k210dd812l8b232ce98d5d162f@mail.gmail.com> <20071212123129.ad22c7ce.darcy@druid.net> <302670f20712121000w6d3c1ecg39620cfa8c39c606@mail.gmail.com> In-Reply-To: <302670f20712121000w6d3c1ecg39620cfa8c39c606@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/74 X-Sequence-Number: 29766 Gary Chambers wrote: > D'Arcy... > >> Have you considered this? > > I considered integrating the begin and end times into the table. I'm > capturing the data via ACPI events, so it's "transactional" by nature. > I want to be able to keep track of false transitions (hence the > is_outage field). > > I'm looking for a way to simplify the transitions output on my web > browser, and I want to combine an offline and a corresponding online > time into a single line. How about a "paired_with" field that references the power_transitions table and a trigger. When you insert a new row, it checks what the previous row was - if it's a down & this is an up, then set the paired_with field on each. -- Richard Huxton Archonet Ltd