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

Commitf30015b

Browse files
committed
Output timestamps in ISO 8601 format when rendering JSON.
Many JSON processors require timestamp strings in ISO 8601 format inorder to convert the strings. When converting a timestamp, with orwithout timezone, to a JSON datum we therefore now use such a formatrather than the type's default text output, in functions such asto_json().This is a change in behaviour from 9.2 and 9.3, as noted in the releasenotes.
1 parent2dfa15d commitf30015b

File tree

5 files changed

+116
-0
lines changed

5 files changed

+116
-0
lines changed

‎doc/src/sgml/release-9.4.sgml

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -162,6 +162,24 @@
162162
</para>
163163
</listitem>
164164

165+
<listitem>
166+
<para>
167+
Values of type
168+
<link linkend="datatype-datetime"><type>timestamp</></link> and
169+
<link linkend="datatype-datetime"><type>timestamptz</></link> are now
170+
rendered in a string format compliant with ISO 8601 rather than the
171+
default output format when converting to or used in
172+
<link linkend="datatype-json"><type>JSON</type></link>.
173+
(Andrew Dunstan)
174+
</para>
175+
176+
<para>
177+
Previously these were rendered in the default text output format
178+
for the type, but many JSON processors require timestamps in ISO 8601
179+
format.
180+
</para>
181+
</listitem>
182+
165183
<listitem>
166184
<para>
167185
Rename <link linkend="SQL-EXPLAIN"><command>EXPLAIN

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

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@
2424
#include"parser/parse_coerce.h"
2525
#include"utils/array.h"
2626
#include"utils/builtins.h"
27+
#include"utils/formatting.h"
2728
#include"utils/lsyscache.h"
2829
#include"utils/json.h"
2930
#include"utils/jsonapi.h"
@@ -53,13 +54,22 @@ typedef enum/* type categories for datum_to_json */
5354
JSONTYPE_NULL,/* null, so we didn't bother to identify */
5455
JSONTYPE_BOOL,/* boolean (built-in types only) */
5556
JSONTYPE_NUMERIC,/* numeric (ditto) */
57+
JSONTYPE_TIMESTAMP,/* we use special formatting for timestamp */
58+
JSONTYPE_TIMESTAMPTZ,/* ... and timestamptz */
5659
JSONTYPE_JSON,/* JSON itself (and JSONB) */
5760
JSONTYPE_ARRAY,/* array */
5861
JSONTYPE_COMPOSITE,/* composite */
5962
JSONTYPE_CAST,/* something with an explicit cast to JSON */
6063
JSONTYPE_OTHER/* all else */
6164
}JsonTypeCategory;
6265

66+
/*
67+
* to_char formats to turn timestamps and timpstamptzs into json strings
68+
* that are ISO 8601 compliant
69+
*/
70+
#defineTS_ISO8601_FMT "\\\"YYYY-MM-DD\"T\"HH24:MI:SS.US\\\""
71+
#defineTSTZ_ISO8601_FMT "\\\"YYYY-MM-DD\"T\"HH24:MI:SS.USOF\\\""
72+
6373
staticinlinevoidjson_lex(JsonLexContext*lex);
6474
staticinlinevoidjson_lex_string(JsonLexContext*lex);
6575
staticinlinevoidjson_lex_number(JsonLexContext*lex,char*s,bool*num_err);
@@ -1262,6 +1272,14 @@ json_categorize_type(Oid typoid,
12621272
*tcategory=JSONTYPE_NUMERIC;
12631273
break;
12641274

1275+
caseTIMESTAMPOID:
1276+
*tcategory=JSONTYPE_TIMESTAMP;
1277+
break;
1278+
1279+
caseTIMESTAMPTZOID:
1280+
*tcategory=JSONTYPE_TIMESTAMPTZ;
1281+
break;
1282+
12651283
caseJSONOID:
12661284
caseJSONBOID:
12671285
*tcategory=JSONTYPE_JSON;
@@ -1375,6 +1393,29 @@ datum_to_json(Datum val, bool is_null, StringInfo result,
13751393
}
13761394
pfree(outputstr);
13771395
break;
1396+
caseJSONTYPE_TIMESTAMP:
1397+
/*
1398+
* The timestamp format used here provides for quoting the string,
1399+
* so no escaping is required.
1400+
*/
1401+
jsontext=DatumGetTextP(
1402+
DirectFunctionCall2(timestamp_to_char,val,
1403+
CStringGetTextDatum(TS_ISO8601_FMT)));
1404+
outputstr=text_to_cstring(jsontext);
1405+
appendStringInfoString(result,outputstr);
1406+
pfree(outputstr);
1407+
pfree(jsontext);
1408+
break;
1409+
caseJSONTYPE_TIMESTAMPTZ:
1410+
/* same comment as for timestamp above */
1411+
jsontext=DatumGetTextP(
1412+
DirectFunctionCall2(timestamptz_to_char,val,
1413+
CStringGetTextDatum(TSTZ_ISO8601_FMT)));
1414+
outputstr=text_to_cstring(jsontext);
1415+
appendStringInfoString(result,outputstr);
1416+
pfree(outputstr);
1417+
pfree(jsontext);
1418+
break;
13781419
caseJSONTYPE_JSON:
13791420
/* JSON and JSONB output will already be escaped */
13801421
outputstr=OidOutputFunctionCall(outfuncoid,val);

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

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -403,6 +403,29 @@ SELECT row_to_json(row((select array_agg(x) as d from generate_series(5,10) x)),
403403
{"f1":[5,6,7,8,9,10]}
404404
(1 row)
405405

406+
-- to_json, timestamps
407+
select to_json(timestamp '2014-05-28 12:22:35.614298');
408+
to_json
409+
------------------------------
410+
"2014-05-28T12:22:35.614298"
411+
(1 row)
412+
413+
BEGIN;
414+
SET LOCAL TIME ZONE 10.5;
415+
select to_json(timestamptz '2014-05-28 12:22:35.614298-04');
416+
to_json
417+
------------------------------------
418+
"2014-05-29T02:52:35.614298+10:30"
419+
(1 row)
420+
421+
SET LOCAL TIME ZONE -8;
422+
select to_json(timestamptz '2014-05-28 12:22:35.614298-04');
423+
to_json
424+
---------------------------------
425+
"2014-05-28T08:22:35.614298-08"
426+
(1 row)
427+
428+
COMMIT;
406429
--json_agg
407430
SELECT json_agg(q)
408431
FROM ( SELECT $$a$$ || x AS b, y AS c,

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

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -403,6 +403,29 @@ SELECT row_to_json(row((select array_agg(x) as d from generate_series(5,10) x)),
403403
{"f1":[5,6,7,8,9,10]}
404404
(1 row)
405405

406+
-- to_json, timestamps
407+
select to_json(timestamp '2014-05-28 12:22:35.614298');
408+
to_json
409+
------------------------------
410+
"2014-05-28T12:22:35.614298"
411+
(1 row)
412+
413+
BEGIN;
414+
SET LOCAL TIME ZONE 10.5;
415+
select to_json(timestamptz '2014-05-28 12:22:35.614298-04');
416+
to_json
417+
------------------------------------
418+
"2014-05-29T02:52:35.614298+10:30"
419+
(1 row)
420+
421+
SET LOCAL TIME ZONE -8;
422+
select to_json(timestamptz '2014-05-28 12:22:35.614298-04');
423+
to_json
424+
---------------------------------
425+
"2014-05-28T08:22:35.614298-08"
426+
(1 row)
427+
428+
COMMIT;
406429
--json_agg
407430
SELECT json_agg(q)
408431
FROM ( SELECT $$a$$ || x AS b, y AS c,

‎src/test/regress/sql/json.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -100,6 +100,17 @@ FROM rows q;
100100

101101
SELECT row_to_json(row((select array_agg(x)as dfrom generate_series(5,10) x)),false);
102102

103+
-- to_json, timestamps
104+
105+
select to_json(timestamp'2014-05-28 12:22:35.614298');
106+
107+
BEGIN;
108+
SET LOCALTIME ZONE10.5;
109+
select to_json(timestamptz'2014-05-28 12:22:35.614298-04');
110+
SET LOCALTIME ZONE-8;
111+
select to_json(timestamptz'2014-05-28 12:22:35.614298-04');
112+
COMMIT;
113+
103114
--json_agg
104115

105116
SELECT json_agg(q)

0 commit comments

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