Thread: Query Assistance
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 (ACpower), 2 = Offline (Battery) sid smallint NOT NULL, -- Timestamp of transition statetime timestamp without timezone 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!
On Wed, 12 Dec 2007 11:58:20 -0500 "Gary Chambers" <gwchamb@gmail.com> wrote: > 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! Have you considered this? CREATE TABLE power_transitions ( -- Transition ID (PK) tid integer NOT NULL, -- Timestamp of power off ( starttimetimestamp without time zone DEFAULT now() NOT NULL, -- Timestamp of power on ( endtime timestamp without timezone, -- Is this a real outage? may not be needed. is_outage boolean DEFAULT true NOT NULL ); The is_outage bool could be handled with special timestamps (e.g. EPOCH) but I am not sure what it signifies to you. Of course you have to deal with false transitions but I don't know what your information capture system is so I can't work out all the details but this seems like a good base for what you want. Every row that has a valid start and end time is a complete record of an outage. I am also thinking of a scheme that uses two tables but I don't really know your environment or requirements. I am assuming that you spend more time querying the table than updating it. If not your problem isn't your database, it's your power plant. :-) -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
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. > I am also thinking of a scheme that uses two tables but I don't really > know your environment or requirements. I am assuming that you spend > more time querying the table than updating it. If not your problem > isn't your database, it's your power plant. :-) It's nothing complex at all. The power in my neighborhood is infamously unstable, and I'm merely keep records to complain as much as I can to the power company. Thanks for taking the time to reply! -- Gary Chambers // Nothing fancy and nothing Microsoft!
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