Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 930259F9DDB for ; Wed, 5 Sep 2007 11:33:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 39563-03 for ; Wed, 5 Sep 2007 11:32:58 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from pih-relay06.plus.net (pih-relay06.plus.net [212.159.14.133]) by postgresql.org (Postfix) with ESMTP id 585FD9F9D45 for ; Wed, 5 Sep 2007 11:32:58 -0300 (ADT) Received: from [84.51.143.99] (helo=server3.office.archonet.com) by pih-relay06.plus.net with esmtp (Exim) id 1ISvw6-0002jP-QQ; Wed, 05 Sep 2007 15:32:54 +0100 Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id 64D8D274057; Wed, 5 Sep 2007 15:32:53 +0100 (BST) Message-ID: <46DEBE15.3050201@archonet.com> Date: Wed, 05 Sep 2007 15:32:53 +0100 From: Richard Huxton User-Agent: Thunderbird 2.0.0.6 (X11/20070728) MIME-Version: 1.0 To: Sabin Coanda CC: pgsql-sql@postgresql.org Subject: Re: ISO time zone format References: <46DE65E2.6090904@archonet.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/51 X-Sequence-Number: 29264 Sabin Coanda wrote: > > I'd just like to format it independent of my DateStyle. Some timestamp parts > may be formatted this way. > > For instance I can format ISO date with to_char( dt, 'YYYY-MM-DD'), and ISO > time with to_char( dt, 'HH24:MI:SS') as well. I'd just like to find the > format pattern for the ISO time zone (numeric). Hmm - we don't seem to support those codes (TZH, TZM) for some reason. http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#sthref405 But it seems the developers know about it: http://www.postgresql.org/docs/faqs.TODO.html http://archives.postgresql.org/pgsql-hackers/2005-12/msg00948.php If you know a little bit of "C" then you should be able to submit a patch for this without too much trouble. For the moment, you'll have to use extract() to pick out the bit(s) you need: to_char(extract(timezone_hour from current_timestamp), 'S00'); HTH -- Richard Huxton Archonet Ltd