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

The following bug has been logged on the website:

Bug reference:      15388
Logged by:          hao.hu
Email address:      huhao0715@gmail.com
PostgreSQL version: 10.1
Operating system:   linux
Description:

1. What did you do?
SET TIME ZONE 'utc';
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE '+8';

2. What did you expect to see?
        timezone
------------------------
 2001-02-16 12:38:40+00

3. What did you see instead?
        timezone
------------------------
 2001-02-17 04:38:40+00

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> 1. What did you do?
> SET TIME ZONE 'utc';
> SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE '+8';

> 2. What did you expect to see?
>         timezone
> ------------------------
>  2001-02-16 12:38:40+00

You're apparently confused about the sign of time zone offsets.
In a POSIX-style zone name, which is what you have there, plus
means west of Greenwich; so this is the right answer:

> 3. What did you see instead?
>         timezone
> ------------------------
>  2001-02-17 04:38:40+00

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.

If you want to use the ISO sign convention, you can do it with an
interval-type zone specification:

regression=# SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE '+8:00'::interval;
        timezone
------------------------
 2001-02-16 12:38:40+00
(1 row)

            regards, tom lane

On 2018-09-17 23:45:05 -0400, Tom Lane wrote:
> =?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.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@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

"Peter J. Holzer" <hjp-pgsql@hjp.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