Re: Document DateStyle effect on jsonpath string()

On Sep 11, 2024, at 10:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> [ looks... ]  Hmm, it looks like jsonb_path_exists_tz is marked
> stable while jsonb_path_exists is claimed to be immutable.
> So yeah, there's a problem here.  I'm not 100% convinced that
> jsonb_path_exists was truly immutable before, but for sure it
> is not now, and that's bad.
>
> regression=# select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
>   jsonb_path_query
> -----------------------
> "2023-08-15 12:34:56"
> (1 row)
>
> regression=# set datestyle = postgres;
> SET
> regression=# select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
>      jsonb_path_query
> ----------------------------
> "Tue Aug 15 12:34:56 2023"
> (1 row)

I wonder, then, whether .string() should be modified to use the ISO format in UTC, and therefore be immutable. That’s
theformat you get if you omit .string() and let result be stringified from a date/time/timestamp. 

FWIW, that’s how my Go port works, since I didn’t bother to replicate the DateStyle GUC (example[1]).

Best,

David

[1]:
https://theory.github.io/sqljson/playground/?p=%2524.timestamp%28%29.string%28%29&j=%25222023-08-15%252012%253A34%253A56%2522&a=&o=1