Thread: Bug on version 12 ?

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

Both are ISO, MDY



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

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

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

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

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

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

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

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