set timestamp oddness

[Will Trillich]

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.../part4-2.phtml

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

[Tom Lane]

Will Trillich <wi**@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

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

http://archives.postgresql.org