Re: Setting timezone: is it bug or intended?

Yura Sokolov wrote:
> 21.03.2011 15:51, Susanne Ebrecht ?????:
> > Hello Yora Sokolov,
> >
> > Maybe this is not a bug - just an inconsistence:
> >
> > Look here (tested with 9.0):
> >
> > =# set timezone='+3.5';
> > SET
> > =# select current_timestamp;
> > now
> > ----------------------------------
> > 2011-03-21 16:05:34.761112+03:30
> > (1 row)
> >
> > In any case, this should get documented.
> >
> > Susanne
> >
> >
> My question were about sign of timezone:
>
> postgres=# set timezone='+03'; -- positive
> postgres=# select now();
> 2011-03-18 11:20:29.696671+03 -- positive
> postgres=# set timezone='+03:00'; -- positive
> 2011-03-18 05:20:35.46008-03 -- negative
>
> Is it intended?

Uh, it certainly looks very odd. What I believe is happening is that
+03:00 is processed as a real time zone specification:

test=> set timezone='+03:00';
SET
test=> show timezone;
TimeZone
----------
--> +03:00
(1 row)

test=> select now();
now
-------------------------------
2011-04-26 15:41:11.409237-03
(1 row)

You will notice that because of the SQL standard, GMT+3 processes as -03
utc_offset, and '+03:00' must be processed the same:

test=> select * from pg_timezone_names;
name | abbrev | utc_offset | is_dst
----------------------------------+--------+------------+--------
...
Etc/GMT+3 | GMT+3 | -03:00:00 | f

The '+03' is seen as hours-minutes-seconds:

test=> set timezone='+03';
SET
test=> show timezone;
TimeZone
----------
--> 03:00:00
(1 row)

test=> select now();
now
-------------------------------
2011-04-26 21:40:57.570654+03
(1 row)

hence the +03. Notice how different that looks from the +03:00 above.

As far as how to even document this, I have no idea. I think this code
is being processed by the timezone library we include as part of Postgres.
I am lost on how to proceed.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +