Re: 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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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