Thread: DST and time zones

Hello,

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.  You can do a

select extract(epoch from now() at time zone 'pst');

Of course this is wrong since DST is in affect, so it should be 'pdt'
not 'pst'.  I understand that postgresql doesn't do this itself, it uses
the OS, however does anyone know a crafty way to get the current time in
any timezone, whether DST is in effect or not?

Im using pg 7.4.3 running on Linux 2.4.25.

Thanks in advance.
A Gilmore

A Gilmore......

> Of course this is wrong since DST is in affect, so it should be 'pdt'
> not 'pst'.  I understand that postgresql doesn't do this itself, it uses
> the OS, however does anyone know a crafty way to get the current time in
> any timezone, whether DST is in effect or not?

I think Debian Linux and FreeBSD have a chose of EST or ESTEDT, the later
gives EDT if it is in effect.  Probably the same thing exists for most
timezones (or at least ones that have areas that may or may not observe
DT.

brew

 ==========================================================================
                  Strange Brew   (brew@theMode.com)
     Check out my Musician's Online Database Exchange (The MODE Pages)
                        http://www.TheMode.com
 ==========================================================================

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

Tom Lane wrote:
> 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';
>
> ....
>
> 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

A Gilmore <agilmore@shaw.ca> writes:
> 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.

This would all work a lot better if the column were timestamp *with*
timezone.  Trying to do it the way you are is just swimming upstream
to no particular purpose.

            regards, tom lane