[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    [GENERAL] time zone problem.
From:       Ulf Mehlig <umehlig () uni-bremen ! de>
Date:       2000-01-29 14:48:18
[Download RAW message or body]


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
----------------------------------------------------------------------

************

[prev in list] [next in list] [prev in thread] [next in thread]