From: Alvaro Herrera <alvherre@surnet.cl>
To: Marc G. Fournier <scrappy@hub.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: using 'zoneinfo' to manipulate dates ...
Date: Sun, 26 Jun 2005 02:15:01 -0400
Message-ID: <20050626061501.GA2446@surnet.cl> (raw)
In-Reply-To: <20050626001729.G993@ganymede.hub.org>
References: <20050626001729.G993@ganymede.hub.org>
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 (<alvherre[a]surnet.cl>)
"Hay quien adquiere la mala costumbre de ser infeliz" (M. A. Evans)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: alvherre@surnet.cl, scrappy@hub.org
Subject: Re: using 'zoneinfo' to manipulate dates ...
In-Reply-To: <20050626061501.GA2446@surnet.cl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox