Setting timezone: is it bug or intended?

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. +