Thread

1. set timestamp oddness

   Will Trillich <will@serensoft.com> — 2004-04-05T21:38:56Z

   not sure what it's supposed to look like, but i'm reasonably
   sure 'show time zone' shouldn't produce this--
   
   	db=# set timezone = '-6';
   	SET
   	db=# show timezone;
   	    TimeZone    
   	----------------
   	 -00:00:00.0216
   	(1 row)
   
   very odd, that! 
   
   i'm using
   
   	$ psql -V
   	psql (PostgreSQL) 7.4.2
   	contains support for command-line editing
   
   on my debian/sarge (testing) system.
   
   
   
   	db=# set timezone to '-5:00';
   	ERROR:  unrecognized time zone name: "-5:00"
   
   pooh. but this works, of course:
   
   	hits=# set timezone = 'America/Chicago';
   	SET
   	hits=# show timezone;
   	    TimeZone     
   	-----------------
   	 America/Chicago
   	(1 row)
   
   (or anything else under /usr/share/zoneinfo...)
   
   
   
   also--
   
   	db=# select now() at time zone 'America/Chicago';
   	ERROR:  time zone "america/chicago" not recognized
   
   doesn't that conflict with the "set" command above? is there a
   good reason for the syntax of the timezone to be different here?
   
   	db=# select now() at time zone '-5';
   	ERROR:  time zone "-5" not recognized
   
   also in conflict with what 'set time zone' accepts...
   
   	db=# select now() at time zone 'GMT-5';
   	ERROR:  time zone "gmt-5" not recognized
   
   
   oh well, it was worth a shot.
   
   but then:
   
   	db=# select now() at time zone 'CDT';
   	          timezone          
   	----------------------------
   	 2004-04-05 16:26:49.991342
   	(1 row)
   
   the one that "set time zone" does NOT accept, "at time zone" DOES.
   hmm?
   
   -- 
   "Why did they hard code that value into the program?".
   "My only guess would be to maximize suckage."
   http://suso.suso.org/docs/apache_and_frontpage/htmldocs/part4-2.phtml
   
   

2. Re: set timestamp oddness

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-04-06T04:50:30Z

   Will Trillich <will@serensoft.com> writes:
   > 	db=# set timezone = '-6';
   > 	SET
   > 	db=# show timezone;
   > 	    TimeZone    
   > 	----------------
   > 	 -00:00:00.0216
   > 	(1 row)
   
   hmm ... I get something reasonable here:
   
   regression=# set timezone = '-6';
   SET
   regression=# show timezone;
    TimeZone
   -----------
    -06:00:00
   (1 row)
   
   Could you poke into this more and find out why it goes wrong on your
   platform?
   
   > 	db=# set timezone to '-5:00';
   > 	ERROR:  unrecognized time zone name: "-5:00"
   
   That one works for me too...
   
   > the one that "set time zone" does NOT accept, "at time zone" DOES.
   > hmm?
   
   This is not totally surprising, seeing that they go through completely
   different code paths.  I believe "set time zone" accepts whatever the
   underlying OS's tzset() routine will take, whereas "at time zone"
   only accepts timezone names listed in datetime.c's builtin table.
   The difference could and someday should be eliminated, but it will
   take rewriting a lot of OS-level timezone support for ourselves :-(
   
   			regards, tom lane