Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit0d57dc2

Browse files
committed
Preserve tz when converting to jsonb timestamptz
This removes an inconsistency in the treatment of different datatypes bythe jsonpath timestamp_tz() function. Conversions from data types thatare not timestamp-aware, such as date and timestamp, are now treatedconsistently with conversion from those that are such as timestamptz.Author: David WheelerReviewed-by: Junwang Zhao and Jeevan ChalkeDiscussion:https://postgr.es/m/7DE080CE-6D8C-4794-9BD1-7D9699172FAB%40justatheory.comBackpatch to release 17.
1 parent71795d1 commit0d57dc2

File tree

2 files changed

+27
-2
lines changed

2 files changed

+27
-2
lines changed

‎src/backend/utils/adt/jsonpath_exec.c

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2707,12 +2707,27 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
27072707
break;
27082708
casejpiTimestampTz:
27092709
{
2710+
structpg_tmtm;
2711+
fsec_tfsec;
2712+
27102713
/* Convert result type to timestamp with time zone */
27112714
switch (typid)
27122715
{
27132716
caseDATEOID:
27142717
checkTimezoneIsUsedForCast(cxt->useTz,
27152718
"date","timestamptz");
2719+
2720+
/*
2721+
* Get the timezone value explicitly since JsonbValue
2722+
* keeps that separate.
2723+
*/
2724+
j2date(DatumGetDateADT(value)+POSTGRES_EPOCH_JDATE,
2725+
&(tm.tm_year),&(tm.tm_mon),&(tm.tm_mday));
2726+
tm.tm_hour=0;
2727+
tm.tm_min=0;
2728+
tm.tm_sec=0;
2729+
tz=DetermineTimeZoneOffset(&tm,session_timezone);
2730+
27162731
value=DirectFunctionCall1(date_timestamptz,
27172732
value);
27182733
break;
@@ -2726,6 +2741,16 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
27262741
caseTIMESTAMPOID:
27272742
checkTimezoneIsUsedForCast(cxt->useTz,
27282743
"timestamp","timestamptz");
2744+
2745+
/*
2746+
* Get the timezone value explicitly since JsonbValue
2747+
* keeps that separate.
2748+
*/
2749+
if (timestamp2tm(DatumGetTimestamp(value),NULL,&tm,
2750+
&fsec,NULL,NULL)==0)
2751+
tz=DetermineTimeZoneOffset(&tm,
2752+
session_timezone);
2753+
27292754
value=DirectFunctionCall1(timestamp_timestamptz,
27302755
value);
27312756
break;

‎src/test/regress/expected/jsonb_jsonpath.out

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2914,7 +2914,7 @@ HINT: Use *_tz() function for time zone support.
29142914
select jsonb_path_query_tz('"2023-08-15"', '$.timestamp_tz()'); -- should work
29152915
jsonb_path_query_tz
29162916
-----------------------------
2917-
"2023-08-15T07:00:00+00:00"
2917+
"2023-08-15T00:00:00-07:00"
29182918
(1 row)
29192919

29202920
select jsonb_path_query('"12:34:56"', '$.timestamp_tz()');
@@ -3101,7 +3101,7 @@ HINT: Use *_tz() function for time zone support.
31013101
select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()'); -- should work
31023102
jsonb_path_query_tz
31033103
-----------------------------
3104-
"2023-08-15T02:34:56+00:00"
3104+
"2023-08-15T12:34:56+10:00"
31053105
(1 row)
31063106

31073107
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()');

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp