From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | PegoraroF10 <marcos(at)f10(dot)com(dot)br> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Bug on version 12 ? |
Date: | 2020-05-15 16:27:40 |
Message-ID: | 12005.1589560060@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
PegoraroF10 <marcos(at)f10(dot)com(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2020-05-15 16:28:34 | Re: Bug on version 12 ? |
Previous Message | PegoraroF10 | 2020-05-15 16:09:21 | Re: Bug on version 12 ? |