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

On 2018-09-17 23:45:05 -0400, Tom Lane wrote:
> =?utf-8?q?PG_Bug_reporting_form?= <noreply(at)postgresql(dot)org> writes:
> > SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE '+8';
[...]
> See
>
> https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-TIMEZONES
>
> notably the comment that
>
> 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. It is
not

* A full time zone name, for example America/New_York. ...

* A time zone abbreviation, for example PST. ...

* [A] ... POSIX-style time zone specifications of the form STDoffset or
STDoffsetDST, ...

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.

Given that this isn't currently covered by the docs and very confusing,
since the sign is the opposite of what PostgreSQL displays timezonetz
values, I propose a fourth format:

* A timezone offset in standard ISO format [+-]HH:MM.

For compatibility with the timezonetz format the :MM part should
probably be optional. I think both the hours and the minutes should be 2
digits, though: '+08', not '+8', '-10:00', not '-10:0')

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>