A Gilmore <agilmore@shaw.ca> writes:
> I need a method of returning a date in a given timezone and accomidating
> DST. For example, my server is set to UTC, Id like to return the epoch
> for Vancouver Canada.
Huh? Epoch is UTC all over the world, at least for sane operating
systems. But disregarding your specific example, the general problem is
valid. I'm afraid there's not a real good solution at the moment.
You should ideally be able to say
select now() at time zone 'PST8PDT';
or one of the other spellings of that DST-aware time zone name, such as
'America/Vancouver'. The raw materials to support this are in place as
of 8.0, but we didn't get all the work done --- maybe 8.1 will be able
to do it.
In the meantime, the only solution I can suggest is pretty klugy:
temporarily set the TIMEZONE variable. For example, I'm in EST5EDT,
so:
regression=# select now();
now
-------------------------------
2004-09-18 15:57:26.944637-04
(1 row)
regression=# begin;
BEGIN
regression=# set local timezone = 'PST8PDT';
SET
regression=# select extract(hour from cast(now() as timestamp without time zone));
date_part
-----------
12
(1 row)
regression=# commit;
COMMIT
regards, tom lane
