From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | nicola(dot)oricchio(at)vertigis(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #18518: ::timestamp add minutes and seconds to the converted values |
Date: | 2024-06-21 14:21:25 |
Message-ID: | ZnWMZc_Umz_U0Int@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)oricchio(at)vertigis(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-06-21 14:50:26 | Re: BUG #18518: ::timestamp add minutes and seconds to the converted values |
Previous Message | Eric Marsden | 2024-06-21 14:02:48 | Cache lookup failed for type 34813 (CREATE TYPE AS ENUM + P/B/E insert, processor-specific) |