Re: jsonpath: Inconsistency of timestamp_tz() Output

On Jul 10, 2024, at 01:48, Junwang Zhao <zhjwpku(at)gmail(dot)com> wrote:

> I apply your patch with some minor change(to make the server not crash):

Oh, thank you! Kicking myself for not catching the obvious.

> It now gives the local tz:
>
> [local] postgres(at)postgres:5432-54960=# set time zone 'America/New_York';
> SET
> Time: 2.894 ms
> [local] postgres(at)postgres:5432-54960=# select
> jsonb_path_query_tz('"2024-08-15 12:34:56"', '$.timestamp_tz()');
> ┌─────────────────────────────┐
> │ jsonb_path_query_tz │
> ├─────────────────────────────┤
> │ "2024-08-15T12:34:56-04:00" │
> └─────────────────────────────┘
> (1 row)
>
> Time: 293813.022 ms (04:53.813)

Yes, and I think that’s what we want, since it preserves and displays the offset for strings that contain them:

david=# set time zone 'America/New_York';
SET
david=# select jsonb_path_query_tz('"2024-08-15 12:34:56+10"', '$.timestamp_tz()');
jsonb_path_query_tz
-----------------------------
"2024-08-15T12:34:56+10:00"

> I'm not sure whether the SQL/JSON standard mentioned this, I searched a
> little bit, but found no clue :(

Yeah I don’t know either, but now at least it’s consistent. I’ve attached a patch to fix it.

Ideally, I think, we wouldn’t convert the value and determine the offset twice, but teach date_timestamptz and timestamp_timestamptz (or date2timestamptz and timestamp2timestamptz?) how to return the offset, or create alternate functions that do so. Not sure what calling style should be adopted here, but this at least addresses the issue. Happy to resubmit something more efficient upon function design feedback.

Best,

David