Thread: timestamp sum question.

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

Original question:
[snip]
> 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?

Larry Rosenman reply:
Look at extract(epoch from ....) to get the number of seconds.

Keith (new):
Thanks,

I've got this far - using "age" function seems to work where subtraction
didn't

select device_name, value, device_id,
sum(age(time,'2008-11-17 14:18:00')) as INTVL
from attribute_change
where attribute='power'
and
time between '2008-11-17 14:18:00' and '2008-11-26'
group by device_name,device_id, value
order by device_id

results
"Lutron Zone 1";"false";837;"00:02:34.125"
"Lutron Zone 1";"true";837;"00:02:53.205"
"Lutron Zone 2";"true";838;"00:02:52.936"
"Lutron Zone 2";"false";838;"00:02:36.392"
"Lutron Zone 3";"false";839;"00:04:00.879"
"Lutron Zone 3";"true";839;"00:02:55.836"

Where the hard coded date values will be replaced by parameters.

What I need to figure out now is how to subtract the sum of the "true"
from the sum of the "false" for each device so the result is the "true"
time.

Keith

On Wed, 26 Nov 2008, Keith Turner wrote:

[snip]
> 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?
Look at extract(epoch from ....) to get the number of seconds.
--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 512-248-2683                 E-Mail: ler@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893