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