Re: BUG #17240: <timestamptz> at time zone ... ; wrong result

Hi!

Thank you for your response.

Next question.

What about the following principle?

if there is a function
y = func( x, z)
and it is linear function (which is the case) then there is inverse
function
x = inverse_func( y, z)
and then the following is always true:
x = inverse_func( func( x, z), z)

Example with plus and minus:
y = x + z
x = y - z
x = (x + z) - z

Postgres follows the principle for valid POSIX values:
func(): timestamptz'2021-10-01 00:00:00 UTC-07:00' at time zone
'UTC' --> timestamp'2021-09-30 17:00:00'
inverse_func(): timestamptz'2021-09-30 17:00:00 UTC' at time zone
'UTC-07:00' --> timestamp'2021-10-01 00:00:00'

And Postgres follows the principle for values like 'America/Los_Angeles'.

But Postgres starts ignoring the principle for invalid POSIX (or should we
say non-POSIX) values:
func(): timestamptz'2021-10-01 00:00:00 -07:00' at time zone
'UTC' --> timestamp'2021-10-01 07:00:00'
inverse_func(): timestamptz'2021-10-01 07:00:00 UTC' at time zone
'-07:00' --> timestamp'2021-10-01 14:00:00'

Please note!
1) Values 'UTC-07:00' and '-07:00' are treated as different values if they
are specified inside a timestamp literal.
And this is expected behaviour. POSIX value and non-POSIX value are
recognized and handled properly.

2) Values 'UTC-07:00' and '-07:00' are treated as equal values if they are
specified as arguments for "at time zone".
This is NOT expected behaviour. Non-POSIX values must not be recognized
and handled as POSIX values.
Also, this behaviour breaks the simple principle described in the
beginning.

To be clear, once again, same examples, different angle.
Next 2 examples produce different result (expected):
timestamptz'2021-10-01 00:00:00 -07:00' at time zone 'UTC' -->
timestamp'2021-10-01 07:00:00'
timestamptz'2021-10-01 00:00:00 UTC-07:00' at time zone 'UTC' -->
timestamp'2021-09-30 17:00:00'

And next 2 examples produce the same result (not expected):
timestamptz'2021-10-01 07:00:00 UTC' at time zone '-07:00' -->
timestamp'2021-10-01 14:00:00'
timestamptz'2021-10-01 07:00:00 UTC' at time zone 'UTC-07:00' -->
timestamp'2021-10-01 14:00:00'

Summary:
I reported this case as the "at time zone" argument is not properly handled.
But you may say that the timestamptz literal is not properly handled.
Anyway, there is no consistent handling as you can see in examples above.

Regards
Marek

Kontakt Tom Lane (<tgl(at)sss(dot)pgh(dot)pa(dot)us>) kirjutas kuupäeval E, 25. oktoober
2021 kell 19:26:

> =?UTF-8?Q?Marek_L=C3=A4ll?= <lall(dot)marek(at)gmail(dot)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
>