Thread: convert sum (interval) to seconds

How can I convert sum(interval) to seconds?


select sum(interval_field) from tablename where condition=condition;

ID  |  LOGIN  | INTERVAL
--------------------------
1   |  JOHN   |  00:00:18
2   |  JOHN   |  00:45:10

If I say, 
select sum(interval) from tablename where login='john';
it will give me 00:45:28 (That's 45 mins, and 28 secs).
How can I convert that to seconds? Can I possibly do it
in just one query? Thank you

On Tue, 2002-09-03 at 11:45, Doppelganger wrote:
> How can I convert sum(interval) to seconds?
...
> If I say, 
> select sum(interval) from tablename where login='john';
> it will give me 00:45:28 (That's 45 mins, and 28 secs).
> How can I convert that to seconds? Can I possibly do it
> in just one query? Thank you

Here's one way; I'm not sure if there may not be something more
efficient, though:
   junk=# select sum(if) from i;      sum       ----------    00:45:28   (1 row)      junk=# select extract (minute
fromsum(if)) * 60 + extract (second   from sum(if)) from i;    ?column?    ----------        2728   (1 row)
 

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "And he said unto his disciples, Therefore I say unto      you, Take no
thoughtfor your life, what ye shall eat;     neither for the body, what ye shall put on. For life      is more than
meat,and the body is more than clothing.     Consider the ravens, for they neither sow nor reap;      they have neither
storehousenor barn; and yet God       feeds them;  how much better you are than the birds!     Consider the lilies, how
theygrow; they toil      not, they spin not; and yet I say unto you, that       Solomon in all his glory was not
arrayedlike one of      these. If then God so clothe the grass, which is to      day in the field, and tomorrow is cast
intothe oven;     how much more will he clothe you, O ye of little       faith?  And seek not what ye shall eat, or
whatye      shall drink, neither be ye of doubtful mind.      But rather seek ye the kingdom of God; and all these
thingsshall be added unto you."                                      Luke 12:22-24; 27-29; 31. 
 

Oliver Elphick <olly@lfix.co.uk> writes:
> On Tue, 2002-09-03 at 11:45, Doppelganger wrote:
>> How can I convert sum(interval) to seconds?

> Here's one way;   
>     junk=# select extract (minute from sum(if)) * 60 + extract (second
>     from sum(if)) from i;

EXTRACT(EPOCH FROM interval) is designed for this ...
        regards, tom lane