Bug on version 12 ?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Bug on version 12 ?

PegoraroF10
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


Reply | Threaded
Open this post in threaded view
|

Re: Bug on version 12 ?

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

select * from pg_settings where name = 'DateStyle';
Reply | Threaded
Open this post in threaded view
|

Re: Bug on version 12 ?

David G Johnston
On Fri, May 15, 2020 at 8:27 AM Michael Lewis <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Bug on version 12 ?

PegoraroF10
In reply to this post by Michael Lewis
Reply | Threaded
Open this post in threaded view
|

Re: Bug on version 12 ?

Tom Lane-2
In reply to this post by PegoraroF10
PegoraroF10 <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Bug on version 12 ?

David G Johnston
In reply to this post by PegoraroF10
On Fri, May 15, 2020 at 8:08 AM PegoraroF10 <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Bug on version 12 ?

PegoraroF10
In reply to this post by Tom Lane-2
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


Reply | Threaded
Open this post in threaded view
|

Re: Bug on version 12 ?

John W Higgins


On Fri, May 15, 2020 at 9:38 AM PegoraroF10 <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Bug on version 12 ?

Adrian Klaver-4
In reply to this post by PegoraroF10
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Bug on version 12 ?

Tom Lane-2
In reply to this post by PegoraroF10
PegoraroF10 <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Bug on version 12 ?

PegoraroF10
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


Reply | Threaded
Open this post in threaded view
|

Re: Bug on version 12 ?

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Bug on version 12 ?

Laurenz Albe
In reply to this post by PegoraroF10
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