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