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 the format 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