PegoraroF10 <marcos@xxxxxxxxxx> 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
