On Jul 10, 2024, at 01:48, Junwang Zhao <zhjw...@gmail.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@postgres:5432-54960=# set time zone 'America/New_York';
> SET
> Time: 2.894 ms
> [local] postgres@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
v1-0001-Preserve-tz-when-converting-to-jsonb-timestamptz.patch
Description: Binary data
