Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 115742E3D1D
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 12 Dec 2007 12:58:59 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 75201-01-3 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 12 Dec 2007 12:58:54 -0400 (AST)
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.190])
	by postgresql.org (Postfix) with ESMTP id A2F952E0099
	for <pgsql-sql@postgresql.org>; Wed, 12 Dec 2007 12:58:22 -0400 (AST)
Received: by nf-out-0910.google.com with SMTP id f5so240270nfh.23
	for <pgsql-sql@postgresql.org>; Wed, 12 Dec 2007 08:58:21 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	bh=ytkcQXZuO3lBrEmod0wbBlV8sjRCEOdWSmdVsMIeWsE=;
	b=u6/Sn0h8TuKuFgPE0vRKyNKPQV4IBxGCsvajk2rfebk+7kT8iItbU12GXUImSFTjCW5udmHU0sdkmBViUP6XbXPpGsCMVhmrOmS+0xJYNwNapuw3Z7PnbNaB8cwa/JiqATSRJ0YryUPpEGvMB2uTd5sFfFVBIfAbsdKupGYIA6g=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=nPBFcBMtGLzJFZws5/QUMxpY3lvDiESaHLzEyPEfJUOl+7+vc1qbtvGzW0NZJjKPYXkYV7S0fCrx2sT0YfRob6aVsbEdYA92OwmrsnvxxOzfHvLOOk9Ay49v8349CeFjCubKdsjPwRR4Cz+e6LAFHYk0rqcGRsrDMuyzEOcB/OE=
Received: by 10.86.100.7 with SMTP id x7mr829967fgb.42.1197478700834;
	Wed, 12 Dec 2007 08:58:20 -0800 (PST)
Received: by 10.86.51.13 with HTTP; Wed, 12 Dec 2007 08:58:20 -0800 (PST)
Message-ID: <302670f20712120858k210dd812l8b232ce98d5d162f@mail.gmail.com>
Date: Wed, 12 Dec 2007 11:58:20 -0500
From: "Gary Chambers" <gwchamb@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Query Assistance
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200712/70
X-Sequence-Number: 29762

All...

I have a simple table in PostgreSQL 8.2.5:

CREATE TABLE power_transitions (
    -- Transition ID (PK)
    tid integer NOT NULL,
    -- State ID (0 = Unknown, 1 = Online (AC power), 2 = Offline (Battery)
    sid smallint NOT NULL,
    -- Timestamp of transition
    statetime timestamp without time zone DEFAULT now() NOT NULL,
    -- Is this a real outage?
    is_outage boolean DEFAULT true NOT NULL
);

It contains a log of power outages (transitions).  I'd like to create
query that returns a transition offline time and associated return to
online time.  Is there a better way of handling this?  I am open to
schema change suggestions.  Thanks very much!

-- Gary Chambers

// Nothing fancy and nothing Microsoft!