Bug on version 12 ?

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

select * from pg_settings where name = 'DateStyle';

On Fri, May 15, 2020 at 8:27 AM Michael Lewis <mlewis@entrata.com> 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.

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

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

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 Fri, May 15, 2020 at 9:38 AM PegoraroF10 <marcos@f10.com.br> 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]https://www.postgresql.org/docs/12/functions-formatting.html - 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

[1]: https://www.postgresql.org/docs/12/functions-formatting.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