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

Commit6dda292

Browse files
committed
Allow datetime values in JsonbValue
SQL/JSON standard allows manipulation with datetime values. So, it appears tobe convinient to allow datetime values to be represented in JsonbValue struct.These datetime values are allowed for temporary representation only. Duringserialization datetime values are converted into strings.SQL/JSON requires writing timestamps with timezone in the same timezone offsetas they were parsed. This is why we allow storage of timezone offset inJsonbValue struct. For the same reason timezone offset argument is added toJsonEncodeDateTime() function.Extracted from original patch by Nikita Glukhov, Teodor Sigaev, Oleg Bartunov.Revised by me. Comments were adjusted by Liudmila Mantrova.Discussion:https://postgr.es/m/fcc6fc6a-b497-f39a-923d-aa34d0c588e8%402ndQuadrant.comDiscussion:https://postgr.es/m/CAPpHfdsZgYEra_PeCLGNoXOWYx6iU-S3wF8aX0ObQUcZU%2B4XTw%40mail.gmail.comAuthor: Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Alexander Korotkov, Liudmila MantrovaReviewed-by: Anastasia Lubennikova, Peter Eisentraut
1 parent5bc4506 commit6dda292

File tree

5 files changed

+94
-13
lines changed

5 files changed

+94
-13
lines changed

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

Lines changed: 26 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1506,23 +1506,23 @@ datum_to_json(Datum val, bool is_null, StringInfo result,
15061506
{
15071507
charbuf[MAXDATELEN+1];
15081508

1509-
JsonEncodeDateTime(buf,val,DATEOID);
1509+
JsonEncodeDateTime(buf,val,DATEOID,NULL);
15101510
appendStringInfo(result,"\"%s\"",buf);
15111511
}
15121512
break;
15131513
caseJSONTYPE_TIMESTAMP:
15141514
{
15151515
charbuf[MAXDATELEN+1];
15161516

1517-
JsonEncodeDateTime(buf,val,TIMESTAMPOID);
1517+
JsonEncodeDateTime(buf,val,TIMESTAMPOID,NULL);
15181518
appendStringInfo(result,"\"%s\"",buf);
15191519
}
15201520
break;
15211521
caseJSONTYPE_TIMESTAMPTZ:
15221522
{
15231523
charbuf[MAXDATELEN+1];
15241524

1525-
JsonEncodeDateTime(buf,val,TIMESTAMPTZOID);
1525+
JsonEncodeDateTime(buf,val,TIMESTAMPTZOID,NULL);
15261526
appendStringInfo(result,"\"%s\"",buf);
15271527
}
15281528
break;
@@ -1550,10 +1550,11 @@ datum_to_json(Datum val, bool is_null, StringInfo result,
15501550

15511551
/*
15521552
* Encode 'value' of datetime type 'typid' into JSON string in ISO format using
1553-
* optionally preallocated buffer 'buf'.
1553+
* optionally preallocated buffer 'buf'. Optional 'tzp' determines time-zone
1554+
* offset (in seconds) in which we want to show timestamptz.
15541555
*/
15551556
char*
1556-
JsonEncodeDateTime(char*buf,Datumvalue,Oidtypid)
1557+
JsonEncodeDateTime(char*buf,Datumvalue,Oidtypid,constint*tzp)
15571558
{
15581559
if (!buf)
15591560
buf=palloc(MAXDATELEN+1);
@@ -1630,11 +1631,30 @@ JsonEncodeDateTime(char *buf, Datum value, Oid typid)
16301631
constchar*tzn=NULL;
16311632

16321633
timestamp=DatumGetTimestampTz(value);
1634+
1635+
/*
1636+
* If a time zone is specified, we apply the time-zone shift,
1637+
* convert timestamptz to pg_tm as if it were without a time
1638+
* zone, and then use the specified time zone for converting
1639+
* the timestamp into a string.
1640+
*/
1641+
if (tzp)
1642+
{
1643+
tz=*tzp;
1644+
timestamp-= (TimestampTz)tz*USECS_PER_SEC;
1645+
}
1646+
16331647
/* Same as timestamptz_out(), but forcing DateStyle */
16341648
if (TIMESTAMP_NOT_FINITE(timestamp))
16351649
EncodeSpecialTimestamp(timestamp,buf);
1636-
elseif (timestamp2tm(timestamp,&tz,&tm,&fsec,&tzn,NULL)==0)
1650+
elseif (timestamp2tm(timestamp,tzp ?NULL :&tz,&tm,&fsec,
1651+
tzp ?NULL :&tzn,NULL)==0)
1652+
{
1653+
if (tzp)
1654+
tm.tm_isdst=1;/* set time-zone presence flag */
1655+
16371656
EncodeDateTime(&tm,fsec, true,tz,tzn,USE_XSD_DATES,buf);
1657+
}
16381658
else
16391659
ereport(ERROR,
16401660
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),

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

Lines changed: 24 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -206,6 +206,24 @@ JsonbTypeName(JsonbValue *jbv)
206206
return"boolean";
207207
casejbvNull:
208208
return"null";
209+
casejbvDatetime:
210+
switch (jbv->val.datetime.typid)
211+
{
212+
caseDATEOID:
213+
return"date";
214+
caseTIMEOID:
215+
return"time without time zone";
216+
caseTIMETZOID:
217+
return"time with time zone";
218+
caseTIMESTAMPOID:
219+
return"timestamp without time zone";
220+
caseTIMESTAMPTZOID:
221+
return"timestamp with time zone";
222+
default:
223+
elog(ERROR,"unrecognized jsonb value datetime type: %d",
224+
jbv->val.datetime.typid);
225+
}
226+
return"unknown";
209227
default:
210228
elog(ERROR,"unrecognized jsonb value type: %d",jbv->type);
211229
return"unknown";
@@ -805,17 +823,20 @@ datum_to_jsonb(Datum val, bool is_null, JsonbInState *result,
805823
break;
806824
caseJSONBTYPE_DATE:
807825
jb.type=jbvString;
808-
jb.val.string.val=JsonEncodeDateTime(NULL,val,DATEOID);
826+
jb.val.string.val=JsonEncodeDateTime(NULL,val,
827+
DATEOID,NULL);
809828
jb.val.string.len=strlen(jb.val.string.val);
810829
break;
811830
caseJSONBTYPE_TIMESTAMP:
812831
jb.type=jbvString;
813-
jb.val.string.val=JsonEncodeDateTime(NULL,val,TIMESTAMPOID);
832+
jb.val.string.val=JsonEncodeDateTime(NULL,val,
833+
TIMESTAMPOID,NULL);
814834
jb.val.string.len=strlen(jb.val.string.val);
815835
break;
816836
caseJSONBTYPE_TIMESTAMPTZ:
817837
jb.type=jbvString;
818-
jb.val.string.val=JsonEncodeDateTime(NULL,val,TIMESTAMPTZOID);
838+
jb.val.string.val=JsonEncodeDateTime(NULL,val,
839+
TIMESTAMPTZOID,NULL);
819840
jb.val.string.len=strlen(jb.val.string.val);
820841
break;
821842
caseJSONBTYPE_JSONCAST:

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

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,9 +14,12 @@
1414
#include"postgres.h"
1515

1616
#include"catalog/pg_collation.h"
17+
#include"catalog/pg_type.h"
1718
#include"miscadmin.h"
1819
#include"utils/builtins.h"
20+
#include"utils/datetime.h"
1921
#include"utils/hashutils.h"
22+
#include"utils/jsonapi.h"
2023
#include"utils/jsonb.h"
2124
#include"utils/memutils.h"
2225
#include"utils/varlena.h"
@@ -244,6 +247,8 @@ compareJsonbContainers(JsonbContainer *a, JsonbContainer *b)
244247
break;
245248
casejbvBinary:
246249
elog(ERROR,"unexpected jbvBinary value");
250+
casejbvDatetime:
251+
elog(ERROR,"unexpected jbvDatetime value");
247252
}
248253
}
249254
else
@@ -1786,6 +1791,22 @@ convertJsonbScalar(StringInfo buffer, JEntry *jentry, JsonbValue *scalarVal)
17861791
JENTRY_ISBOOL_TRUE :JENTRY_ISBOOL_FALSE;
17871792
break;
17881793

1794+
casejbvDatetime:
1795+
{
1796+
charbuf[MAXDATELEN+1];
1797+
size_tlen;
1798+
1799+
JsonEncodeDateTime(buf,
1800+
scalarVal->val.datetime.value,
1801+
scalarVal->val.datetime.typid,
1802+
&scalarVal->val.datetime.tz);
1803+
len=strlen(buf);
1804+
appendToBuffer(buffer,buf,len);
1805+
1806+
*jentry=len;
1807+
}
1808+
break;
1809+
17891810
default:
17901811
elog(ERROR,"invalid jsonb scalar type");
17911812
}

‎src/include/utils/jsonapi.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -161,6 +161,7 @@ extern Jsonb *transform_jsonb_string_values(Jsonb *jsonb, void *action_state,
161161
externtext*transform_json_string_values(text*json,void*action_state,
162162
JsonTransformStringValuesActiontransform_action);
163163

164-
externchar*JsonEncodeDateTime(char*buf,Datumvalue,Oidtypid);
164+
externchar*JsonEncodeDateTime(char*buf,Datumvalue,Oidtypid,
165+
constint*tzp);
165166

166167
#endif/* JSONAPI_H */

‎src/include/utils/jsonb.h

Lines changed: 21 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -241,7 +241,15 @@ enum jbvType
241241
jbvArray=0x10,
242242
jbvObject,
243243
/* Binary (i.e. struct Jsonb) jbvArray/jbvObject */
244-
jbvBinary
244+
jbvBinary,
245+
246+
/*
247+
* Virtual types.
248+
*
249+
* These types are used only for in-memory JSON processing and serialized
250+
* into JSON strings when outputted to json/jsonb.
251+
*/
252+
jbvDatetime=0x20,
245253
};
246254

247255
/*
@@ -282,11 +290,21 @@ struct JsonbValue
282290
intlen;
283291
JsonbContainer*data;
284292
}binary;/* Array or object, in on-disk format */
293+
294+
struct
295+
{
296+
Datumvalue;
297+
Oidtypid;
298+
int32typmod;
299+
inttz;/* Numeric time zone, in seconds, for
300+
* TimestampTz data type */
301+
}datetime;
285302
}val;
286303
};
287304

288-
#defineIsAJsonbScalar(jsonbval)((jsonbval)->type >= jbvNull && \
289-
(jsonbval)->type <= jbvBool)
305+
#defineIsAJsonbScalar(jsonbval)(((jsonbval)->type >= jbvNull && \
306+
(jsonbval)->type <= jbvBool) || \
307+
(jsonbval)->type == jbvDatetime)
290308

291309
/*
292310
* Key/value pair within an Object.

0 commit comments

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