Обсуждение: BUG #17687: Session timezone change does not play well with prepared statements

The following bug has been logged on the website:

Bug reference:      17687
Logged by:          Bart Lengkeek
Email address:      bart@lengkeek.net
PostgreSQL version: 15.1
Operating system:   Linux (Ubuntu 20.04.5 LTS)
Description:

When a statement is prepared using one session timezone and executed using
another, it returns the wrong timestamp.

Example using psql:
=====================================================
psql (15.1 (Ubuntu 15.1-1.pgdg20.04+1))

postgres=# SET TIMEZONE TO 'Europe/Berlin';
SET
postgres=# PREPARE test AS
   SELECT '2021-03-15 14:10:03'::timestamptz;
PREPARE
postgres=# SET TIMEZONE TO 'America/Chicago';
SET
postgres=# EXECUTE test;
      timestamptz       
------------------------
 2021-03-15 08:10:03-05
(1 row)

postgres=# SELECT '2021-03-15 14:10:03'::timestamptz;
      timestamptz       
------------------------
 2021-03-15 14:10:03-05
(1 row)
=====================================================

The "EXECUTE test" should return the same as the unprepared execution of the
same statement, "2021-03-15 14:10:03-05" instead of "2021-03-15
08:10:03-05".

The same behavior happens for protocol level prepared statements. That's
actually how I found out about this strange behavior. It also applies to
server version 13.9.

Thnx for looking into this.

PG Bug reporting form <noreply@postgresql.org> writes:
> When a statement is prepared using one session timezone and executed using
> another, it returns the wrong timestamp.

> Example using psql:
> postgres=# SET TIMEZONE TO 'Europe/Berlin';
> SET
> postgres=# PREPARE test AS
>    SELECT '2021-03-15 14:10:03'::timestamptz;
> PREPARE
> postgres=# SET TIMEZONE TO 'America/Chicago';
> SET
> postgres=# EXECUTE test;
>       timestamptz       
> ------------------------
>  2021-03-15 08:10:03-05
> (1 row)

That is not a bug, it's just how timestamptz works.  You'd get the
same behavior if you stored the value into a table and then read
it out under another timezone setting.  (Internally, the reason
is that the timestamptz literal is reduced to a Const on sight,
and stored that way in the prepared statement.)

            regards, tom lane