Thread: BUG #17240: at time zone ... ; wrong result

The following bug has been logged on the website:

Bug reference:      17240
Logged by:          Marek Läll
Email address:      marek.lall@eesti.ee
PostgreSQL version: 13.4
Operating system:   Debian
Description:

Hello!

# SELECT version();
                                                        version
                                           

------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 13.4 (Debian 13.4-1.pgdg100+1) on aarch64-unknown-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)


# SET TimeZone='UTC';

# select * from pg_timezone_names where name like 'America/Los_Angeles';
        name         | abbrev | utc_offset | is_dst 
---------------------+--------+------------+--------
 America/Los_Angeles | PDT    | -07:00:00  | t
(1 row)

# select now()
 , now() at time zone 'America/Los_Angeles' as correct
 , now() at time zone '-07:00:00' as wrong;

              now              |          correct           |
wrong            
-------------------------------+----------------------------+----------------------------
 2021-10-21 09:29:07.591962+00 | 2021-10-21 02:29:07.591962 | 2021-10-21
16:29:07.591962
(1 row)

--> COLUMN "WRONG" IS EXPECTED TO HAVE THE SAME VALUE AS COLUMN "CORRECT"

# select timestamptz'2021-10-01 00:00:00 UTC' at time zone
'America/Los_Angeles' as correct_1
 , timestamptz'2021-10-01 00:00:00 UTC' at time zone '-07:00' as wrong_1
 , timestamptz'2021-10-01 00:00:00 America/Los_Angeles' at time zone
'America/Los_Angeles' as correct_2
 , timestamptz'2021-10-01 00:00:00 -07:00:00' at time zone '-07:00' as
wrong_2
 , timestamptz'2021-10-01 00:00:00 -07:00:00' at time zone
'America/Los_Angeles' as correct_3
 , timestamptz'2021-10-01 00:00:00 America/Los_Angeles' at time zone
'-07:00' as wrong_3;
 
      correct_1      |       wrong_1       |      correct_2      |
wrong_2       |      correct_3      |       wrong_3       

---------------------+---------------------+---------------------+---------------------+---------------------+---------------------
 2021-09-30 17:00:00 | 2021-10-01 07:00:00 | 2021-10-01 00:00:00 |
2021-10-01 14:00:00 | 2021-10-01 00:00:00 | 2021-10-01 14:00:00
(1 row)

--> COLUMNS "WRONG_<N>" ARE EXPECTED TO HAVE THE SAME VALUE AS COLUMN
"CORRECT_<N>"

PG Bug reporting form <noreply@postgresql.org> writes:
> # select now()
>  , now() at time zone 'America/Los_Angeles' as correct
>  , now() at time zone '-07:00:00' as wrong;

Unfortunately, the pure-numeric syntax for time zone names follows the
POSIX sign convention, which is opposite to the ISO convention used
in pg_timezone_names.utc_offset (and in most other places in Postgres).
So "at time zone '+07:00:00'" is what you needed to write to duplicate
the 'America/Los_Angeles' result.  See

https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES

Now, if you'd done this:

select ... now() at time zone interval '-07:00:00' as fine

you'd have gotten the ISO sign interpretation.  But an undecorated
literal string defaults to being of type text, meaning you get
the time-zone-name logic path.

The great thing about standards is there are so many to choose from :-(

            regards, tom lane

=?UTF-8?Q?Marek_L=C3=A4ll?= <lall.marek@gmail.com> writes:
> POSIX string (syntax) is defined as:
>  --> stdoffset[dst[offset][,start-date[/time],end-date[/time]]]

> The std string specifies the name of the time zone.
> It must be three or more characters long and must not contain a leading
> colon, embedded digits, commas, nor plus and minus signs.

Hmm, you're reading the POSIX spec I guess, because our docs don't
say that ;-).  The IANA tzdb code only enforces that STD not be empty,
and Postgres has modified it to allow empty STD as well.  That's
an ancient backwards-compatibility decision that we likely ought
to change sometime, so I've intentionally not documented it in
appendix B.5 [1].  The text in B.5 actually says that you need angle
brackets if you want any non-letters in STD or DST, which is more
conservative than what the IANA code will accept.

> 2) then: ignores the fact that string '-07:00' is invalid POSIX value

It's valid according to our interpretation of POSIX.  Some experimentation
suggests that GNU date(1) enforces the POSIX definition exactly, which is
that STD be at least three characters, all alphabetic.  That implies that
they wrote their own TZ parser, because the IANA reference code doesn't
act that way.

            regards, tom lane

[1] https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html