From: Tom Lane <tgl@sss.pgh.pa.us>
To: bart@lengkeek.net
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17687: Session timezone change does not play well with
 prepared statements
In-reply-to: <17687-97355f89274fa9a3@postgresql.org>
References: <17687-97355f89274fa9a3@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Mon, 14 Nov 2022 07:20:57 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <888266.1668435625.1@sss.pgh.pa.us>
Date: Mon, 14 Nov 2022 09:20:25 -0500
Message-ID: <888267.1668435625@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/888267.1668435625%40sss.pgh.pa.us>
Precedence: bulk

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