On Fri, May 15, 2020 at 8:08 AM PegoraroF10 <marcos@xxxxxxxxxx> 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.
