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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

A Gilmore......

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 (AT) theMode (DOT) com)
Check out my Musician's Online Database Exchange (The MODE Pages)
http://www.TheMode.com
================================================== ========================


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

A Gilmore <agilmore (AT) shaw (DOT) ca> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

A Gilmore <agilmore (AT) shaw (DOT) ca> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org