Hi,
I have a table with these fields, simplified here
Device (string name of device)
Power State (String "on" or "off")
Timestamp
Every time a device is turned on or off a record is written to the
database.
What I am trying to do is find a way to define the amount of time that
each device is "on", that is the sum of the period of time between each
"on" and "off" record for that device. I'm not sure how to iterate
through the table and then sum the values. There is also the problem of
initial state, if the first value is "off" it can be assumed to be "on"
or the first value is "on" it can assumed to be "off".
Can this be done in straight SQL, or should we need to use a
programmatic solution? I want to use this in a BIRT report dataset.
One way to do it is to sum the "on" items and sum the "off" items and
subtract the difference - but could still use some syntax help.
Is it even possible to use sum for timestamps in postgres? Is there a
way to turn the timestamp values to integers and back again?
Keith