pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
convert sum (interval) to seconds 3+ messages / 3 participants [nested] [flat]
* convert sum (interval) to seconds @ 2002-09-03 10:45 Doppelganger <quadratini@sni.ph> 0 siblings, 1 reply; 3+ messages in thread From: Doppelganger @ 2002-09-03 10:45 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: convert sum (interval) to seconds @ 2002-09-03 11:11 Oliver Elphick <olly@lfix.co.uk> parent: Doppelganger <quadratini@sni.ph> 0 siblings, 1 reply; 3+ messages in thread From: Oliver Elphick @ 2002-09-03 11:11 UTC (permalink / raw) To: Doppelganger <quadratini@sni.ph>; +Cc: pgsql-sql 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 from sum(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 thought for 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 storehouse nor barn; and yet God feeds them; how much better you are than the birds! Consider the lilies, how they grow; they toil not, they spin not; and yet I say unto you, that Solomon in all his glory was not arrayed like one of these. If then God so clothe the grass, which is to day in the field, and tomorrow is cast into the oven; how much more will he clothe you, O ye of little faith? And seek not what ye shall eat, or what ye shall drink, neither be ye of doubtful mind. But rather seek ye the kingdom of God; and all these things shall be added unto you." Luke 12:22-24; 27-29; 31. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: convert sum (interval) to seconds @ 2002-09-03 13:37 Tom Lane <tgl@sss.pgh.pa.us> parent: Oliver Elphick <olly@lfix.co.uk> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2002-09-03 13:37 UTC (permalink / raw) To: Oliver Elphick <olly@lfix.co.uk>; +Cc: Doppelganger <quadratini@sni.ph>; pgsql-sql 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-09-03 13:37 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2002-09-03 10:45 convert sum (interval) to seconds Doppelganger <quadratini@sni.ph> 2002-09-03 11:11 ` Oliver Elphick <olly@lfix.co.uk> 2002-09-03 13:37 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox