pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17687: Session timezone change does not play well with prepared statements
2+ messages / 2 participants
[nested] [flat]
* BUG #17687: Session timezone change does not play well with prepared statements
@ 2022-11-14 07:20 PG Bug reporting form <noreply@postgresql.org>
2022-11-14 14:20 ` Re: BUG #17687: Session timezone change does not play well with prepared statements Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2022-11-14 07:20 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: bart@lengkeek.net
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #17687: Session timezone change does not play well with prepared statements
2022-11-14 07:20 BUG #17687: Session timezone change does not play well with prepared statements PG Bug reporting form <noreply@postgresql.org>
@ 2022-11-14 14:20 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2022-11-14 14:20 UTC (permalink / raw)
To: bart@lengkeek.net; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-11-14 14:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-14 07:20 BUG #17687: Session timezone change does not play well with prepared statements PG Bug reporting form <noreply@postgresql.org>
2022-11-14 14:20 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox