From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: marek.lall@eesti.ee
Subject: BUG #17240: <timestamptz> at time zone ... ; wrong result
Date: Thu, 21 Oct 2021 09:48:39 +0000
Message-ID: <17240-cdccc24b9eee2d2c@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17240
Logged by: Marek Läll
Email address: marek.lall@eesti.ee
PostgreSQL version: 13.4
Operating system: Debian
Description:
Hello!
# SELECT version();
version
------------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.4 (Debian 13.4-1.pgdg100+1) on aarch64-unknown-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)
# SET TimeZone='UTC';
# select * from pg_timezone_names where name like 'America/Los_Angeles';
name | abbrev | utc_offset | is_dst
---------------------+--------+------------+--------
America/Los_Angeles | PDT | -07:00:00 | t
(1 row)
# select now()
, now() at time zone 'America/Los_Angeles' as correct
, now() at time zone '-07:00:00' as wrong;
now | correct |
wrong
-------------------------------+----------------------------+----------------------------
2021-10-21 09:29:07.591962+00 | 2021-10-21 02:29:07.591962 | 2021-10-21
16:29:07.591962
(1 row)
--> COLUMN "WRONG" IS EXPECTED TO HAVE THE SAME VALUE AS COLUMN "CORRECT"
# select timestamptz'2021-10-01 00:00:00 UTC' at time zone
'America/Los_Angeles' as correct_1
, timestamptz'2021-10-01 00:00:00 UTC' at time zone '-07:00' as wrong_1
, timestamptz'2021-10-01 00:00:00 America/Los_Angeles' at time zone
'America/Los_Angeles' as correct_2
, timestamptz'2021-10-01 00:00:00 -07:00:00' at time zone '-07:00' as
wrong_2
, timestamptz'2021-10-01 00:00:00 -07:00:00' at time zone
'America/Los_Angeles' as correct_3
, timestamptz'2021-10-01 00:00:00 America/Los_Angeles' at time zone
'-07:00' as wrong_3;
correct_1 | wrong_1 | correct_2 |
wrong_2 | correct_3 | wrong_3
---------------------+---------------------+---------------------+---------------------+---------------------+---------------------
2021-09-30 17:00:00 | 2021-10-01 07:00:00 | 2021-10-01 00:00:00 |
2021-10-01 14:00:00 | 2021-10-01 00:00:00 | 2021-10-01 14:00:00
(1 row)
--> COLUMNS "WRONG_<N>" ARE EXPECTED TO HAVE THE SAME VALUE AS COLUMN
"CORRECT_<N>"
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, marek.lall@eesti.ee
Subject: Re: BUG #17240: <timestamptz> at time zone ... ; wrong result
In-Reply-To: <17240-cdccc24b9eee2d2c@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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