time zone problem. - Mailing list pgsql-general

Hello out there,

I have a problem using time zones. I did the following:

First, I set the time zone to GMT:

   db=> set time zone 'GMT';
   db=> show time zone;
   NOTICE:  Time zone is GMT

Then, I created a table with a datetime column and inserted some
values with different time zone specifications:

   db=> create table tz_test (d datetime);
   db=> insert into tz_test values ('01.01.2000 00:00:00.0 GMT');
   db=> insert into tz_test values ('01.01.2000 00:00:00.0 CET');
   db=> insert into tz_test values ('01.01.2000 00:00:00.0 AST');
   db=> select * from tz_test;
   d
   --------------------------
   01.01.2000 00:00:00.00 GMT
   31.12.1999 23:00:00.00 GMT
   01.01.2000 04:00:00.00 GMT

Seems to work. Now I set the time zone to Central European time. Seems
to work, too, now the 2nd value is 00:00, and the others are printed
relative to CET:

   db=> set time zone 'CET';
   db=> show time zone;
   NOTICE:  Time zone is CET
   db=> select * from tz_test;
   d
   --------------------------
   01.01.2000 01:00:00.00 CET
   01.01.2000 00:00:00.00 CET
   01.01.2000 05:00:00.00 CET

But if I want to do the same with Atlantic Standard time, I get
something different:

   db=> set time zone 'AST';
   db=> show time zone;
   NOTICE:  Time zone is AST
   db=> select * from tz_test;
   d
   --------------------------
   01.01.2000 00:00:00.00 AST
   31.12.1999 23:00:00.00 AST
   01.01.2000 04:00:00.00 AST

Apparently pgsql did not recognize AST and jumped back to GMT, didn't
it? In fact, you can set time zone to everything, and pgsql won't
complain. Do you have any suggestions? And, BTW, is there a
possibility to select the wanted time zone in the query? Would be nice
if one is playing around with various time zones in the same query
but wants relative time information.

I use "PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc
egcs-2.91.66. DateStyle is German with European conventions."

Many thanks for your help,
Ulf


P.S.: In case you have an answer, please CC: me, I'm not on the list
      at the moment! Thank you!

--
======================================================================
Ulf Mehlig    <umehlig@zmt.uni-bremen.de>
              Center for Tropical Marine Ecology/ZMT, Bremen, Germany
----------------------------------------------------------------------