Thread: Setting timezone: is it bug or intended?
postgres=# set timezone='+03';
SET
postgres=# select now();
now
-------------------------------
2011-03-18 11:20:29.696671+03
(1 row)
postgres=# set timezone='+03:00';
SET
postgres=# select now();
now
------------------------------
2011-03-18 05:20:35.46008-03
(1 row)
(checked with 8.4 and 9.0)
Hello Yora Sokolov,
On 18.03.2011 09:22, Yura Sokolov wrote:
>
> postgres=# set timezone='+03:00';
> SET
> postgres=# select now();
> now
> ------------------------------
> 2011-03-18 05:20:35.46008-03
> (1 row)
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
--
Susanne Ebrecht - 2ndQuadrant
PostgreSQL Development, 24x7 Support, Training and Services
www.2ndQuadrant.com
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? Yura Sokolov
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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes: > As far as how to even document this, I have no idea. It already is documented. See http://developer.postgresql.org/pgdocs/postgres/datatype-datetime.html#DATATYPE-TIMEZONES specifically the point that POSIX zone names have the opposite sign convention from ISO-8601. The great thing about standards is there are so many to choose from ;-) regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > As far as how to even document this, I have no idea. > > It already is documented. See > http://developer.postgresql.org/pgdocs/postgres/datatype-datetime.html#DATATYPE-TIMEZONES > specifically the point that POSIX zone names have the opposite sign > convention from ISO-8601. > > The great thing about standards is there are so many to choose from ;-) What isn't documented is why the sign changes for +0300 but not +03: test=> set timezone='+03:00'; SET test=> select now(); now ------------------------------- 2011-04-26 18:22:55.571638-03 (1 row) test=> set timezone='+03'; SET test=> select now(); now ------------------------------- 2011-04-27 00:23:00.627179+03 (1 row) It is the colon somehow: test=> set timezone='+03:'; ERROR: invalid value for parameter "TimeZone": "+03:" test=> select now(); now ------------------------------- 2011-04-26 18:24:36.921323-03 (1 row) test=> set timezone='+03:0'; SET test=> select now(); now ------------------------------ 2011-04-26 18:25:09.88588-03 (1 row) -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes: > Tom Lane wrote: >> It already is documented. See >> http://developer.postgresql.org/pgdocs/postgres/datatype-datetime.html#DATATYPE-TIMEZONES >> specifically the point that POSIX zone names have the opposite sign >> convention from ISO-8601. >> >> The great thing about standards is there are so many to choose from ;-) > What isn't documented is why the sign changes for +0300 but not +03: +03:00 is a legal POSIX zone name (hence the sign is different from SQL convention). The other one is evidently being handled by this code path in check_timezone: /* * Try it as a numeric number of hours (possibly fractional). */ hours = strtod(*newval, &endptr); if (endptr != *newval && *endptr == '\0') { /* Here we change from SQL to Unix sign convention */ myextra.CTimeZone = -hours * SECS_PER_HOUR; myextra.HasCTZSet = true; } which I think is legacy code meant to deal with SQL-standard specification of timezone offsets as INTERVAL values. You get the same interpretation of sign when you use the SQL-spec syntax: regression=# set time zone interval '+03:00'; SET regression=# select now(); now ------------------------------- 2011-04-27 00:44:53.560295+03 (1 row) Like I said, too many standards with their fingers in this pie. regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Tom Lane wrote: > >> It already is documented. See > >> http://developer.postgresql.org/pgdocs/postgres/datatype-datetime.html#DATATYPE-TIMEZONES > >> specifically the point that POSIX zone names have the opposite sign > >> convention from ISO-8601. > >> > >> The great thing about standards is there are so many to choose from ;-) > > > What isn't documented is why the sign changes for +0300 but not +03: > > +03:00 is a legal POSIX zone name (hence the sign is different from SQL > convention). The other one is evidently being handled by this code path > in check_timezone: > > /* > * Try it as a numeric number of hours (possibly fractional). > */ > hours = strtod(*newval, &endptr); > if (endptr != *newval && *endptr == '\0') > { > /* Here we change from SQL to Unix sign convention */ > myextra.CTimeZone = -hours * SECS_PER_HOUR; > myextra.HasCTZSet = true; > } > > which I think is legacy code meant to deal with SQL-standard > specification of timezone offsets as INTERVAL values. You get the same > interpretation of sign when you use the SQL-spec syntax: > > regression=# set time zone interval '+03:00'; > SET > regression=# select now(); > now > ------------------------------- > 2011-04-27 00:44:53.560295+03 > (1 row) > > Like I said, too many standards with their fingers in this pie. I assume we decided we can't improve this. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +