Re: BUG #17240: at time zone ... ; wrong result - Mailing list pgsql-bugs

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.

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