pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedusing 'zoneinfo' to manipulate dates ...
2+ messages / 2 participants
[nested] [flat]
* using 'zoneinfo' to manipulate dates ...
@ 2005-06-26 03:24 Marc G. Fournier <scrappy@hub.org>
2005-06-26 06:15 ` Re: using 'zoneinfo' to manipulate dates ... Alvaro Herrera <alvherre@surnet.cl>
0 siblings, 1 reply; 2+ messages in thread
From: Marc G. Fournier @ 2005-06-26 03:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: using 'zoneinfo' to manipulate dates ...
2005-06-26 03:24 using 'zoneinfo' to manipulate dates ... Marc G. Fournier <scrappy@hub.org>
@ 2005-06-26 06:15 ` Alvaro Herrera <alvherre@surnet.cl>
0 siblings, 0 replies; 2+ messages in thread
From: Alvaro Herrera @ 2005-06-26 06:15 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@hub.org>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-06-26 06:15 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-26 03:24 using 'zoneinfo' to manipulate dates ... Marc G. Fournier <scrappy@hub.org>
2005-06-26 06:15 ` Alvaro Herrera <alvherre@surnet.cl>
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