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 49E0052862 for ; Sun, 26 Jun 2005 03:15:13 -0300 (ADT) 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 82379-05 for ; Sun, 26 Jun 2005 06:15:04 +0000 (GMT) Received: from mr1.surnet.cl (smtp1.surnet.cl [216.155.73.162]) by svr1.postgresql.org (Postfix) with ESMTP id B286852863 for ; Sun, 26 Jun 2005 03:15:03 -0300 (ADT) Received: from smtp1.surnet.cl (216.155.73.168) by mr1.surnet.cl (7.0.031.3) id 42587EDE010AE0C0; Sun, 26 Jun 2005 02:14:27 -0400 Received: from smtp2.surnet.cl (smtp2.surnet.cl []) by mr1.surnet.cl ([216.155.73.168]); Sun, 26 Jun 2005 06:14:26 +0000 Received: from cluster.surnet.cl (216.155.73.164) by smtp2.surnet.cl (7.0.031.3) id 4259ADE300A2FF58; Sun, 26 Jun 2005 02:14:59 -0400 Received: from localhost (200.85.220.31) by cluster.surnet.cl (7.0.043) id 42B3EF600011CB13; Sun, 26 Jun 2005 02:14:59 -0400 Received: by localhost (Postfix, from userid 1000) id 5A061C2DC68; Sun, 26 Jun 2005 02:15:01 -0400 (CLT) Date: Sun, 26 Jun 2005 02:15:01 -0400 From: Alvaro Herrera To: "Marc G. Fournier" Cc: pgsql-sql@postgresql.org Subject: Re: using 'zoneinfo' to manipulate dates ... Message-ID: <20050626061501.GA2446@surnet.cl> References: <20050626001729.G993@ganymede.hub.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050626001729.G993@ganymede.hub.org> User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.619 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, FORGED_RCVD_HELO X-Spam-Level: * X-Archive-Number: 200506/302 X-Sequence-Number: 21903 On Sun, Jun 26, 2005 at 12:24:53AM -0300, Marc G. Fournier wrote: > > Does anyone have a 'table of timezones' that could be used to "localize" > as part of a query? > > For instance, something like: > > SELECT date > FROM table > WHERE ( time || ' ' || ( SELECT timezone > FROM zones > WHERE id = table.timezone )) = '2004-12-12'; You can do this very easily in CVS tip. I assume you want something like this: alvherre=# show timezone; TimeZone ------------------ America/Santiago (1 fila) alvherre=# select '00:00:00 GMT'::timetz at time zone 'Chile/EasterIsland'; timezone ------------- 18:00:00-06 (1 fila) alvherre=# select '00:00:00 GMT'::timetz at time zone 'Chile/Continental'; timezone ------------- 20:00:00-04 (1 fila) Not sure if that helps you any. I think you could build a table using this and the fact that "postmaster -d3" prints the whole table of known timezones. (You could extract that bit from the zic files as well.) -- Alvaro Herrera () "Hay quien adquiere la mala costumbre de ser infeliz" (M. A. Evans)