public inbox for [email protected]
help / color / mirror / Atom feedFrom: David E. Wheeler <[email protected]>
To: Junwang Zhao <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: jsonpath: Inconsistency of timestamp_tz() Output
Date: Fri, 19 Jul 2024 10:05:25 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAEG8a3J2rBZ-S2VpwJCsWoLSmdSqWfnd0QH9kO_YE4ZJFSqGXQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAEG8a3+QHYaLk+g8_LDnpbPMEC-Ht7-mOV2OsvTqqCmbWWJhew@mail.gmail.com>
<[email protected]>
<CAEG8a3LB530nDGiwV+xTchGtGZ2ZE79WX_oKvFKKTxbDDeZScw@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
On Jul 10, 2024, at 11:19, David E. Wheeler <[email protected]> wrote:
> Oh, and the time and date were wrong, too, because I blindly used the same conversion for dates as for timestamps. Fixed in v2.
>
> PR: https://github.com/theory/postgres/pull/7
> CF: https://commitfest.postgresql.org/49/5119/
Rebase on 5784a49. No other changes. I would consider this a bug in features added for 17.
Best,
David
Attachments:
[application/octet-stream] v3-0001-Preserve-tz-when-converting-to-jsonb-timestamptz.patch (4.1K, ../[email protected]/2-v3-0001-Preserve-tz-when-converting-to-jsonb-timestamptz.patch)
download | inline diff:
From 7f037618a85a1ac5363ba6485d0f51bb4c22137b Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <[email protected]>
Date: Wed, 10 Jul 2024 11:17:54 -0400
Subject: [PATCH v3] Preserve tz when converting to jsonb timestamptz
The JSONB jbvDatetime type has a field for offset, and displays the time
in that offset. For example, when the time zone GUC is set to
America/New_York, the jsonpath `timestamp_tz()` method returns a value
that displays a parsed value with its offset, not the local offset:
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"
This was not true for values parsed by `timestamp_tz()` that lacked an
offset. It correctly assumes the local time zone, but displays it in
UTC:
david=# select jsonb_path_query_tz('"2024-08-15 12:34:56"', '$.timestamp_tz()');
jsonb_path_query_tz
-----------------------------
"2024-08-15T16:34:56+00:00"
To fix this inconsistent behavior, determine the offset for values being
cast from `DATEOID` and `DATEOID` types to `jpiTimestampTz` and store it
in the resulting jbvDatetime value. With this change, the result now
preserves the offset just as it does when converting from offset-aware
values:
david=# select jsonb_path_query_tz('"2024-08-15 12:34:56"', '$.timestamp_tz()');
jsonb_path_query_tz
-----------------------------
"2023-08-15T12:34:56-04:00"
Author: David Wheeler
Reviewed-by: Junwang Zhao
Discussion: https://postgr.es/m/7DE080CE-6D8C-4794-9BD1-7D9699172FAB%40justatheory.com
---
src/backend/utils/adt/jsonpath_exec.c | 12 ++++++++++++
src/test/regress/expected/jsonb_jsonpath.out | 4 ++--
2 files changed, 14 insertions(+), 2 deletions(-)
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index d79c929822..ac174bbaa6 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -2707,12 +2707,21 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
break;
case jpiTimestampTz:
{
+ struct pg_tm tm;
+ fsec_t fsec;
/* Convert result type to timestamp with time zone */
switch (typid)
{
case DATEOID:
checkTimezoneIsUsedForCast(cxt->useTz,
"date", "timestamptz");
+ DateADT dateVal = DatumGetDateADT(value);
+ j2date(dateVal + POSTGRES_EPOCH_JDATE,
+ &(tm.tm_year), &(tm.tm_mon), &(tm.tm_mday));
+ tm.tm_hour = 0;
+ tm.tm_min = 0;
+ tm.tm_sec = 0;
+ tz = DetermineTimeZoneOffset(&tm, session_timezone);
value = DirectFunctionCall1(date_timestamptz,
value);
break;
@@ -2726,6 +2735,9 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
case TIMESTAMPOID:
checkTimezoneIsUsedForCast(cxt->useTz,
"timestamp", "timestamptz");
+ if (timestamp2tm(DatumGetTimestamp(value), NULL, &tm, &fsec, NULL, NULL) == 0) {
+ tz = DetermineTimeZoneOffset(&tm, session_timezone);
+ }
value = DirectFunctionCall1(timestamp_timestamptz,
value);
break;
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index 7bb4eb1bc2..02abaac689 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2964,7 +2964,7 @@ HINT: Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15"', '$.timestamp_tz()'); -- should work
jsonb_path_query_tz
-----------------------------
- "2023-08-15T07:00:00+00:00"
+ "2023-08-15T00:00:00-07:00"
(1 row)
select jsonb_path_query('"12:34:56"', '$.timestamp_tz()');
@@ -3151,7 +3151,7 @@ HINT: Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()'); -- should work
jsonb_path_query_tz
-----------------------------
- "2023-08-15T02:34:56+00:00"
+ "2023-08-15T12:34:56+10:00"
(1 row)
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()');
--
2.45.2
view thread (10+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: jsonpath: Inconsistency of timestamp_tz() Output
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox