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 35D9F52846 for ; Sun, 26 Jun 2005 00:25:05 -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 46884-02 for ; Sun, 26 Jun 2005 03:25:03 +0000 (GMT) Received: from hub.org (hub.org [200.46.204.220]) by svr1.postgresql.org (Postfix) with ESMTP id 9329E52816 for ; Sun, 26 Jun 2005 00:25:03 -0300 (ADT) Received: from localhost (unknown [200.46.204.144]) by hub.org (Postfix) with ESMTP id 91B4664B8EE for ; Sun, 26 Jun 2005 00:25:03 -0300 (ADT) Received: from hub.org ([200.46.204.220]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 46218-04 for ; Sun, 26 Jun 2005 03:24:54 +0000 (GMT) Received: from ganymede.hub.org (blk-224-176-51.eastlink.ca [24.224.176.51]) by hub.org (Postfix) with ESMTP id D467664B8BF for ; Sun, 26 Jun 2005 00:24:54 -0300 (ADT) Received: by ganymede.hub.org (Postfix, from userid 1000) id 869BD486B0; Sun, 26 Jun 2005 00:24:53 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 827CB35192 for ; Sun, 26 Jun 2005 00:24:53 -0300 (ADT) Date: Sun, 26 Jun 2005 00:24:53 -0300 (ADT) From: "Marc G. Fournier" To: pgsql-sql@postgresql.org Subject: using 'zoneinfo' to manipulate dates ... Message-ID: <20050626001729.G993@ganymede.hub.org> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.159 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200506/301 X-Sequence-Number: 21902 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'; Something like this, but that works: # select ( now() || ' ' || 'PST8PDT' )::timestamp; ERROR: invalid input syntax for type timestamp: "2005-06-26 00:23:29.789703-03 PST8PDT" ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664