Re: Setting timezone: is it bug or intended?

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