Обсуждение: using 'zoneinfo' to manipulate dates ...

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

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)