Re: BUG #18518: ::timestamp add minutes and seconds to the converted values - Mailing list pgsql-bugs
From | Bruce Momjian |
---|---|
Subject | Re: BUG #18518: ::timestamp add minutes and seconds to the converted values |
Date | |
Msg-id | ZnWMZc_Umz_U0Int@momjian.us Whole thread Raw |
In response to | BUG #18518: ::timestamp add minutes and seconds to the converted values (PG Bug reporting form <noreply@postgresql.org>) |
List | pgsql-bugs |
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.
pgsql-bugs by date: