Thread: Bug on version 12 ?

Bug on version 12 ?

From
PegoraroF10
Date:
select To_Json(Current_Timestamp);
returns "2020-05-15T14:49:44.266342+00:00" on version 11.7 or 12.3

So I have lots of JSONS which have timestamp on them.

select 
  JS ->> 'mydate'::text, 
  to_timestamp((JS ->> 'mydate'), 'YYYY-MM-DD HH24:MI:SS')
from (select '{"somefield": true, "otherfield": true, "mydate":
"2020-04-02T18:26:50.941531-03:00"}'::jsonb) j(JS)

This SQL works fine on 11.7 but not on 12.3 version.

ERROR: invalid value "T1" for "HH24"
Detail: Value must be an integer.
Where: SQL function "castimmutabletimestamp" statement 1

Is that a version 12 bug or a server configuration ?



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html



Re: Bug on version 12 ?

From
Michael Lewis
Date:
Just wonder, have you compared these on the two servers?

select * from pg_settings where name = 'DateStyle';

Re: Bug on version 12 ?

From
"David G. Johnston"
Date:
On Fri, May 15, 2020 at 8:27 AM Michael Lewis <mlewis@entrata.com> wrote:
Just wonder, have you compared these on the two servers?

select * from pg_settings where name = 'DateStyle';

The OP is using to_timestamp, the DateStyle setting is immaterial.

David J.

Re: Bug on version 12 ?

From
PegoraroF10
Date:

Re: Bug on version 12 ?

From
Tom Lane
Date:
PegoraroF10 <marcos@f10.com.br> writes:
> select
>   JS ->> 'mydate'::text,
>   to_timestamp((JS ->> 'mydate'), 'YYYY-MM-DD HH24:MI:SS')
> from (select '{"somefield": true, "otherfield": true, "mydate":
> "2020-04-02T18:26:50.941531-03:00"}'::jsonb) j(JS)

> This SQL works fine on 11.7 but not on 12.3 version.

Stripping away the JSON frippery, what you have is

to_timestamp('2020-04-02T18:26:50.941531-03:00', 'YYYY-MM-DD HH24:MI:SS')

which used to be allowed, but v12 is pickier; it insists that you account
for the "T" explicitly:

regression=# select to_timestamp('2020-04-02T18:26:50.941531-03:00', 'YYYY-MM-DD HH24:MI:SS');
ERROR:  invalid value "T1" for "HH24"
DETAIL:  Value must be an integer.
regression=# select to_timestamp('2020-04-02T18:26:50.941531-03:00', 'YYYY-MM-DDTHH24:MI:SS');
      to_timestamp
------------------------
 2020-04-02 00:06:50-04
(1 row)

I think you're doing it wrong and you should just cast to timestamp:

regression=# select
  JS ->> 'mydate'::text,
  (JS ->> 'mydate')::timestamptz
from (select '{"somefield": true, "otherfield": true, "mydate":
"2020-04-02T18:26:50.941531-03:00"}'::jsonb) j(JS)
;
             ?column?             |          timestamptz
----------------------------------+-------------------------------
 2020-04-02T18:26:50.941531-03:00 | 2020-04-02 17:26:50.941531-04
(1 row)

Use of to_timestamp() to parse a bog-standard time format is a
classic antipattern IMO.  It is inflexible, it doesn't detect
the errors you'd actually like it to detect, and it is harder
to type --- so why do people insist on doing it like that?

            regards, tom lane



Re: Bug on version 12 ?

From
"David G. Johnston"
Date:
On Fri, May 15, 2020 at 8:08 AM PegoraroF10 <marcos@f10.com.br> wrote:
select To_Json(Current_Timestamp);
returns "2020-05-15T14:49:44.266342+00:00" on version 11.7 or 12.3

So I have lots of JSONS which have timestamp on them.

select
  JS ->> 'mydate'::text,
  to_timestamp((JS ->> 'mydate'), 'YYYY-MM-DD HH24:MI:SS')
from (select '{"somefield": true, "otherfield": true, "mydate":
"2020-04-02T18:26:50.941531-03:00"}'::jsonb) j(JS)

This SQL works fine on 11.7 but not on 12.3 version.

ERROR: invalid value "T1" for "HH24"
Detail: Value must be an integer.
Where: SQL function "castimmutabletimestamp" statement 1

Is that a version 12 bug or a server configuration ?

Its a version 12 behavior change, though its somewhat unfortunate that its covered by the imprecise:
"Adjust to_timestamp()/to_date() functions to be more forgiving of template mismatches ..." item in the release notes.

I believe (cannot test at the moment) that the issue is that the code no longer likes to match space template markers with non-space input, skipping the template position altogether without moving along the input string.  You will want to change your template to use "T" which more closely matches the input data anwyay.

Order you can, and probably should, just stop using to_timestamp and do ("JS->>'mydate)::timestamptz (which has the added benefit of keeping the timezone information).

David J.

Re: Bug on version 12 ?

From
PegoraroF10
Date:
Ok Tom but then you cannot go back and forth, like this ...

select to_timestamp(jsonb_build_object('mydate',
current_timestamp)->>'mydate', 'YYYY-MM-DD HH24:MI:SS');

works on 11.7 but not on 12.3.





--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html



Re: Bug on version 12 ?

From
John W Higgins
Date:


On Fri, May 15, 2020 at 9:38 AM PegoraroF10 <marcos@f10.com.br> wrote:
Ok Tom but then you cannot go back and forth, like this ...

select to_timestamp(jsonb_build_object('mydate',
current_timestamp)->>'mydate', 'YYYY-MM-DD HH24:MI:SS');

From here [1] - there are 2 green boxes on the page marked "Tip" - the second one is of interest here.

Apparently the portable format for your need would be

select to_timestamp('2020-04-02T18:26:50.941531-03:00', 'YYYY-MM-DDtHH24:MI:SS');

That works on both PG 11 and PG 12.

John W Higgins

Re: Bug on version 12 ?

From
Adrian Klaver
Date:
On 5/15/20 9:38 AM, PegoraroF10 wrote:
> Ok Tom but then you cannot go back and forth, like this ...
> 
> select to_timestamp(jsonb_build_object('mydate',
> current_timestamp)->>'mydate', 'YYYY-MM-DD HH24:MI:SS');

select jsonb_build_object('mydate',
current_timestamp);
                jsonb_build_object
------------------------------------------------
  {"mydate": "2020-05-15T10:54:17.913596-07:00"}
(1 row)

Option 1:

select to_timestamp(jsonb_build_object('mydate',
current_timestamp)->>'mydate', 'YYYY-MM-DD T HH24:MI:SS');
       to_timestamp
-------------------------
  05/15/2020 10:54:20 PDT




Option 2 per Tom's suggestion:

select (jsonb_build_object('mydate',
current_timestamp)->>'mydate')::timestamptz;
           timestamptz
--------------------------------
  05/15/2020 10:54:58.649859 PDT


> 
> works on 11.7 but not on 12.3.
> 
> 
> 
> 
> 
> --
> Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Bug on version 12 ?

From
Tom Lane
Date:
PegoraroF10 <marcos@f10.com.br> writes:
> Ok Tom but then you cannot go back and forth, like this ...
> select to_timestamp(jsonb_build_object('mydate',
> current_timestamp)->>'mydate', 'YYYY-MM-DD HH24:MI:SS');

> works on 11.7 but not on 12.3.

The advice I gave you was to stop using to_timestamp altogether.
That would work fine on any Postgres version.

            regards, tom lane



Re: Bug on version 12 ?

From
PegoraroF10
Date:
I understood, but the problem is that I cannot just migrate from 11 to 12, I
have to carefully verify all code before migration.



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html



Re: Bug on version 12 ?

From
Adrian Klaver
Date:
On 5/15/20 12:35 PM, PegoraroF10 wrote:
> I understood, but the problem is that I cannot just migrate from 11 to 12, I
> have to carefully verify all code before migration.

It would be helpful to include the information you are responding to.

The simplest case works on 11:

select version();
                                                              version 


---------------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 11.8 (Ubuntu 11.8-1.pgdg18.04+1) on x86_64-pc-linux-gnu, 
compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit

select (jsonb_build_object('mydate',
test(# current_timestamp)->>'mydate')::timestamptz;
           timestamptz
-------------------------------
  2020-05-15 12:55:26.259151-07
(1 row)

> 
> 
> 
> --
> Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Bug on version 12 ?

From
Laurenz Albe
Date:
On Fri, 2020-05-15 at 12:35 -0700, PegoraroF10 wrote:
> I understood, but the problem is that I cannot just migrate from 11 to 12, I
> have to carefully verify all code before migration.

That is always required.  Owing to cour careful testing, you found an
incompatibility, and you have to adapt your code to it.

That said, I feel your pain at this unexpected incompatibility.
We try not to introduce incompatibility if we don't think that the
advantages outweigh the disadvantages, and in this case the new
behavior seems cleaner and following the documentation more closely.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com