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 46FDD3A2C82 for ; Sun, 2 Jan 2005 16:39:59 +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 24074-01 for ; Sun, 2 Jan 2005 16:39:50 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id E94643A2C50 for ; Sun, 2 Jan 2005 16:39:49 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j02Gdf9N037534 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Sun, 2 Jan 2005 09:39:44 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j02Gdfp1037286; Sun, 2 Jan 2005 09:39:41 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j02Gdf38037285; Sun, 2 Jan 2005 09:39:41 -0700 (MST) (envelope-from mfuhr) Date: Sun, 2 Jan 2005 09:39:40 -0700 From: Michael Fuhr To: Karel Zak Cc: Jamison Roberts , List pgsql-sql Subject: Re: Formatting an Interval Message-ID: <20050102163940.GA37196@winnie.fuhr.org> References: <238e52230412310837183fb730@mail.gmail.com> <20041231194118.GA23987@winnie.fuhr.org> <1104682763.3610.2.camel@petra> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1104682763.3610.2.camel@petra> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/4 X-Sequence-Number: 19792 On Sun, Jan 02, 2005 at 05:19:23PM +0100, Karel Zak wrote: > On Fri, 2004-12-31 at 12:41 -0700, Michael Fuhr wrote: > > > 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". TO_CHAR() works with several types, including integer, numeric, and double precision. If you've broken the interval into three variables, one containing hours, one containing minutes, and one containing seconds, then you can use TO_CHAR() to add leading zeroes where needed. -- Michael Fuhr http://www.fuhr.org/~mfuhr/