diff options
| author | Andrew Dunstan <andrew@dunslane.net> | 2024-07-30 07:57:38 -0400 |
|---|---|---|
| committer | Andrew Dunstan <andrew@dunslane.net> | 2024-07-30 07:57:38 -0400 |
| commit | 524d490a9f4ab81d86bbedc6e429fbc27776351c (patch) | |
| tree | 4046749ff81cda4525940207c81e161799d2d2cc | |
| parent | 06ffce4559d0f7d29e0d92d18b72b4b96772ad1c (diff) | |
| download | postgresql-524d490a9f4ab81d86bbedc6e429fbc27776351c.tar.gz postgresql-524d490a9f4ab81d86bbedc6e429fbc27776351c.zip | |
Preserve tz when converting to jsonb timestamptz
This removes an inconsistency in the treatment of different datatypes by
the jsonpath timestamp_tz() function. Conversions from data types that
are not timestamp-aware, such as date and timestamp, are now treated
consistently with conversion from those that are such as timestamptz.
Author: David Wheeler
Reviewed-by: Junwang Zhao and Jeevan Chalke
Discussion: https://postgr.es/m/7DE080CE-6D8C-4794-9BD1-7D9699172FAB%40justatheory.com
Backpatch to release 17.
| -rw-r--r-- | src/backend/utils/adt/jsonpath_exec.c | 25 | ||||
| -rw-r--r-- | src/test/regress/expected/jsonb_jsonpath.out | 4 |
2 files changed, 27 insertions, 2 deletions
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c index d79c9298227..c47221b7ee7 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -2707,12 +2707,27 @@ 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"); + + /* + * Get the timezone value explicitly since JsonbValue + * keeps that separate. + */ + j2date(DatumGetDateADT(value) + 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 +2741,16 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, case TIMESTAMPOID: checkTimezoneIsUsedForCast(cxt->useTz, "timestamp", "timestamptz"); + + /* + * Get the timezone value explicitly since JsonbValue + * keeps that separate. + */ + 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 7bb4eb1bc27..02abaac6891 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()'); |