X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A8C323A2C55 for ; Sun, 2 Jan 2005 16:13:56 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 15984-04 for ; Sun, 2 Jan 2005 16:13:47 +0000 (GMT) Received: from smtp.etmail.cz (smtp.etmail.cz [160.218.43.220]) by svr1.postgresql.org (Postfix) with ESMTP id 5F2193A2C85 for ; Sun, 2 Jan 2005 16:13:41 +0000 (GMT) Received: from 10.162.199.75 (85-160-30-175.eurotel.cz [85.160.30.175]) by smtp.etmail.cz (Postfix) with ESMTP id 3088C19416A; Sun, 2 Jan 2005 17:13:37 +0100 (CET) Subject: Re: Formatting an Interval From: Karel Zak To: Michael Fuhr Cc: Jamison Roberts , List pgsql-sql In-Reply-To: <20041231194118.GA23987@winnie.fuhr.org> References: <238e52230412310837183fb730@mail.gmail.com> <20041231194118.GA23987@winnie.fuhr.org> Content-Type: text/plain Date: Sun, 02 Jan 2005 17:19:23 +0100 Message-Id: <1104682763.3610.2.camel@petra> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-3) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.424 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/3 X-Sequence-Number: 19791 On Fri, 2004-12-31 at 12:41 -0700, Michael Fuhr wrote: > On Fri, Dec 31, 2004 at 11:37:32AM -0500, Jamison Roberts wrote: > > > All of the functions that i've looked at seem to only extract parts > > from Intervals. What I need to do is to format the interval. For > > instance, I have a Interval with the value 1 day 07:57:52. I would > > like that in HH:MM:SS. So in the example the output would be > > 31:57:52. > > I'm not aware of a built-in way to get such a format; somebody > please correct me if I'm mistaken. > > You could write a function to format the interval. For example, > with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to > convert the interval to a number of seconds; convert that to hours, > minutes, and seconds; and use TO_CHAR to format the return value. to_char() works with standard date/time ranges, for example 1-24 -- so there is no way how convert to anything like "31:57:52". Karel -- Karel Zak