Thread: BUG #18518: ::timestamp add minutes and seconds to the converted values

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

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.

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