pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18518: ::timestamp add minutes and seconds to the converted values
3+ messages / 3 participants
[nested] [flat]
* BUG #18518: ::timestamp add minutes and seconds to the converted values
@ 2024-06-20 15:33 PG Bug reporting form <noreply@postgresql.org>
2024-06-21 14:21 ` Re: BUG #18518: ::timestamp add minutes and seconds to the converted values Bruce Momjian <bruce@momjian.us>
2024-06-21 14:50 ` Re: BUG #18518: ::timestamp add minutes and seconds to the converted values Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2024-06-20 15:33 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: nicola.oricchio@vertigis.com
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18518: ::timestamp add minutes and seconds to the converted values
2024-06-20 15:33 BUG #18518: ::timestamp add minutes and seconds to the converted values PG Bug reporting form <noreply@postgresql.org>
@ 2024-06-21 14:21 ` Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 3+ messages in thread
From: Bruce Momjian @ 2024-06-21 14:21 UTC (permalink / raw)
To: nicola.oricchio@vertigis.com; pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18518: ::timestamp add minutes and seconds to the converted values
2024-06-20 15:33 BUG #18518: ::timestamp add minutes and seconds to the converted values PG Bug reporting form <noreply@postgresql.org>
@ 2024-06-21 14:50 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2024-06-21 14:50 UTC (permalink / raw)
To: nicola.oricchio@vertigis.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> select dat, (dat at time zone 'utc')::timestamp without time zone from aaa
> order by 1;
This will involve a time zone conversion from UTC to your current
timezone setting. Possibly that's different between your two
installations? The first results you show are consistent with
Europe/Berlin.
> 1892-12-31 23:00:00 | 1893-12-31 23:53:28 <-- it should be 1893-01-01 00:00:00
This is not a bug, nor is it a recent behavioral change. If you don't
like it, you can go complain to IANA's timezone database crew [1],
but your odds of getting them to change it are about zero IMO. Their
practice is to use a zone offset based on the representative city's
local mean solar time in years before that area adopted standardized
timezones. You'll find the same behavior in any other software that
depends on the IANA tz data. For instance, on my Linux workstation:
$ TZ=America/New_York date --date='1892-12-31 23:00:00 UTC'
Sat Dec 31 18:00:00 EST 1892
$ TZ=Europe/Berlin date --date='1892-12-31 23:00:00 UTC'
Sat Dec 31 23:53:28 LMT 1892
New York is not less wonky than Berlin; they just adopted a
standard zone a few years sooner, so you have to go back
a little further to see the "LMT" output:
$ TZ=America/New_York date --date='1880-12-31 23:00:00 UTC'
Fri Dec 31 18:03:58 LMT 1880
regards, tom lane
[1] https://www.iana.org/time-zones
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-06-21 14:50 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-20 15:33 BUG #18518: ::timestamp add minutes and seconds to the converted values PG Bug reporting form <noreply@postgresql.org>
2024-06-21 14:21 ` Bruce Momjian <bruce@momjian.us>
2024-06-21 14:50 ` 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