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 /src/backend/utils/adt/jsonpath_exec.c | |
| 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.
Diffstat (limited to 'src/backend/utils/adt/jsonpath_exec.c')
| -rw-r--r-- | src/backend/utils/adt/jsonpath_exec.c | 25 |
1 files changed, 25 insertions, 0 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; |