Re: BUG #15388: time convert error when use AT TIME ZONE '+8'

"Peter J. Holzer" <hjp-pgsql(at)hjp(dot)at> writes:
> On 2018-09-17 23:45:05 -0400, Tom Lane wrote:
>> Another issue to keep in mind is that in POSIX time zone names,
>> positive offsets are used for locations west of Greenwich. Everywhere
>> else, PostgreSQL follows the ISO-8601 convention that positive timezone
>> offsets are east of Greenwich.

> I notice that '+8' doesn't match any of the documented formats.

[ pokes around for awhile... ] You're right, but there's more there than
meets the eye. Purely-numeric zone specifications tend to have different
interpretations depending on context. Datetime input has one set of
conventions, cf table 8.12 here:
https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-INPUT
while the timezone parameter has some other conventions documented on the
SET reference page. Meanwhile, AT TIME ZONE doesn't do either of those
things but goes straight for the symbolic case (cf timestamp_zone()).
It's all kind of a mess.

> It is obviously parsed as the last one, with STD as an empty strings,
> but POSIX specifies that this must be "no less than three, nor more than
> {TZNAME_MAX}, bytes", so an empty STD isn't allowed.

Yeah. That was intentional, as localtime.c contains this comment:

/* we allow empty STD abbrev, unlike IANA */

but I wonder whether it's such a hot idea. The other types of
purely-numeric zone specifications seem to all follow the ISO sign
convention, but this allows purely-numeric input that uses the POSIX
sign convention. I wonder if we should revert this code to match the
IANA upstream, so as to reduce confusion. But then AT TIME ZONE '+8'
would fail, unless we stuck additional code in there. (Then again,
failing might be better than silently changing sign convention.)

In any case, it might be a good idea for section 8.5.3 to mention that
it's not trying to cover numeric zone specifications.

regards, tom lane