Re: DST and time zones

Tom Lane wrote:
> A Gilmore <agilmore(at)shaw(dot)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';
>
> ....
>
> 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
>

My misuse of the term epoch has to do with the kluge Ive been
considering using to get javascript to reconize timezones without using
the local system (you cannot set the time zone in js), I shouldn't have
used it for the example.

Instead of setting the TIMEZONE variable, is there a way I can return a
given timezone's offset instead? Maybe Im just dull today but Im having
trouble picturing how setting the local timezone will accomplish what I
need.

I have table holding timestamps without a time zone (considered GMT).
Ill being making inserts into this table with a timestamp like 3pm PST,
which I need to be translated and inserted as GMT. Later this will
likely be queried where I need the timestamp returned for say EST.

Thanks for the help.
A Gilmore