Date: Fri, 21 Jun 2024 10:21:25 -0400
From: Bruce Momjian <bruce@momjian.us>
To: nicola.oricchio@vertigis.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18518: ::timestamp add minutes and seconds to the converted
 values
Message-ID: <ZnWMZc_Umz_U0Int@momjian.us>
References: <18518-e16f6cc69c9ce413@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <18518-e16f6cc69c9ce413@postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/ZnWMZc_Umz_U0Int%40momjian.us>
Precedence: bulk

On Thu, Jun 20, 2024 at 03:33:29PM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      18518
> Logged by:          Nicola Oricchio
> Email address:      nicola.oricchio@vertigis.com
> PostgreSQL version: 12.18
> Operating system:   Ubuntu
> Description:        
> 
> repro:
> create table aaa (dat timestamp without time zone);
> insert into aaa values ('1892-12-31 23:00:00');
> insert into aaa values ('1893-12-31 23:00:00');
> insert into aaa values ('1992-12-31 13:33:12');
> select dat, (dat at time zone 'utc')::timestamp without time zone from aaa
> order by 1;
> 
> output:
>          dat         |      timezone
> ---------------------+---------------------
>  1892-12-31 23:00:00 | 1893-12-31 23:53:28 <-- it should be 1893-01-01
> 00:00:00
>  1893-12-31 23:00:00 | 1894-01-01 00:00:00
>  1992-12-31 13:33:12 | 1992-12-31 14:33:12
> 
> it works correctly with 12.2
>          dat         |      timezone
> ---------------------+---------------------
>  1892-12-31 23:00:00 | 1893-01-01 00:00:00
>  1893-12-31 23:00:00 | 1894-01-01 00:00:00
>  1992-12-31 13:33:12 | 1992-12-31 14:33:12
> 
> time zone is CET

I just ran your test on PG 12.19, and got the right results in the
US/Eastern time zone:

	$ psql test
	psql (12.19)
	Type "help" for help.
	
	test=> SELECT version();
	                                          version
	--------------------------------------------------------------------------------------------
	 PostgreSQL 12.19 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
	(1 row)
	
	create table aaa (dat timestamp without time zone);
	insert into aaa values ('1892-12-31 23:00:00');
	insert into aaa values ('1893-12-31 23:00:00');
	insert into aaa values ('1992-12-31 13:33:12');
	select dat, (dat at time zone 'utc')::timestamp without time zone from aaa
	order by 1;
	
	         dat         |      timezone
	---------------------+---------------------
	 1892-12-31 23:00:00 | 1892-12-31 18:00:00
	 1893-12-31 23:00:00 | 1893-12-31 18:00:00
	 1992-12-31 13:33:12 | 1992-12-31 08:33:12

I am guessing that your time zone database changed in 12.18 as mentioned
in the 12.18 release notes:

	Author: Tom Lane <tgl@sss.pgh.pa.us>
	Branch: master [272a7c303] 2024-02-01 15:57:53 -0500
	Branch: REL_16_STABLE [b4fb76fb5] 2024-02-01 15:57:53 -0500
	Branch: REL_15_STABLE [970b1aeeb] 2024-02-01 15:57:53 -0500
	Branch: REL_14_STABLE [35b8b2c4a] 2024-02-01 15:57:53 -0500
	Branch: REL_13_STABLE [ac3afc3f7] 2024-02-01 15:57:53 -0500
	Branch: REL_12_STABLE [b59ae79b7] 2024-02-01 15:57:53 -0500

	      Update time zone data files to <application>tzdata</application>
	      release 2024a for DST law changes in Greenland, Kazakhstan, and
	      Palestine, plus corrections for the Antarctic stations Casey and
	      Vostok.  Also historical corrections for Vietnam, Toronto, and
	      Miquelon.

I would look at changes in the $PG_INSTALLDIR/share/timezonesets/ files
for your session timezone value to see the changes that happened between
those two releases.  There are other time zone updates in other 12.*
releases so I don't know which one affected you.  If you download 12.2
and 12.18 and do a diff of $PG_INSTALLDIR/share/timezonesets/ you should
see the changes.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.