On 05/27/2014 07:25 PM, Andrew Dunstan wrote:
On 05/27/2014 07:17 PM, Tom Lane wrote:
Stephen Frost <sfr...@snowman.net> writes:
* Andrew Dunstan (and...@dunslane.net) wrote:
Given that this would be a hard coded behaviour change, is it too
late to do this for 9.4?
No, for my 2c.
If we do it by adding casts then it'd require an initdb, so I'd vote
against that for 9.4. If we just change behavior in json.c then that
objection doesn't apply, so I wouldn't complain.
I wasn't proposing to add a cast, just to allow users to add one if
they wanted. But I'm quite happy to go with special-casing timestamps,
and leave the larger question for another time.
Here's a draft patch. I'm still checking to see if there are other
places that need to be fixed, but I think this has the main one.
cheers
andrew
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index a7364f3..d262bda 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -24,6 +24,7 @@
#include "parser/parse_coerce.h"
#include "utils/array.h"
#include "utils/builtins.h"
+#include "utils/formatting.h"
#include "utils/lsyscache.h"
#include "utils/json.h"
#include "utils/jsonapi.h"
@@ -53,6 +54,8 @@ typedef enum /* type categories for datum_to_json */
JSONTYPE_NULL, /* null, so we didn't bother to identify */
JSONTYPE_BOOL, /* boolean (built-in types only) */
JSONTYPE_NUMERIC, /* numeric (ditto) */
+ JSONTYPE_TIMESTAMP, /* we use special formatting for timestamp */
+ JSONTYPE_TIMESTAMPTZ, /* ... and timestamptz */
JSONTYPE_JSON, /* JSON itself (and JSONB) */
JSONTYPE_ARRAY, /* array */
JSONTYPE_COMPOSITE, /* composite */
@@ -60,6 +63,13 @@ typedef enum /* type categories for datum_to_json */
JSONTYPE_OTHER /* all else */
} JsonTypeCategory;
+/*
+ * to_char formats to turn timestamps and timpstamptzs into json strings
+ * that are ISO 8601 compliant
+ */
+#define TS_ISO8601_FMT "\\\"YYYY-MM-DD\"T\"HH24:MI:SS.US\\\""
+#define TSTZ_ISO8601_FMT "\\\"YYYY-MM-DD\"T\"HH24:MI:SS.USOF\\\""
+
static inline void json_lex(JsonLexContext *lex);
static inline void json_lex_string(JsonLexContext *lex);
static inline void json_lex_number(JsonLexContext *lex, char *s, bool *num_err);
@@ -1262,6 +1272,14 @@ json_categorize_type(Oid typoid,
*tcategory = JSONTYPE_NUMERIC;
break;
+ case TIMESTAMPOID:
+ *tcategory = JSONTYPE_TIMESTAMP;
+ break;
+
+ case TIMESTAMPTZOID:
+ *tcategory = JSONTYPE_TIMESTAMPTZ;
+ break;
+
case JSONOID:
case JSONBOID:
*tcategory = JSONTYPE_JSON;
@@ -1375,6 +1393,29 @@ datum_to_json(Datum val, bool is_null, StringInfo result,
}
pfree(outputstr);
break;
+ case JSONTYPE_TIMESTAMP:
+ /*
+ * The timestamp format used here provides for quoting the string,
+ * so no escaping is required.
+ */
+ jsontext = DatumGetTextP(
+ DirectFunctionCall2(timestamp_to_char, val,
+ CStringGetTextDatum(TS_ISO8601_FMT)));
+ outputstr = text_to_cstring(jsontext);
+ appendStringInfoString(result, outputstr);
+ pfree(outputstr);
+ pfree(jsontext);
+ break;
+ case JSONTYPE_TIMESTAMPTZ:
+ /* same comment as for timestamp above */
+ jsontext = DatumGetTextP(
+ DirectFunctionCall2(timestamptz_to_char, val,
+ CStringGetTextDatum(TSTZ_ISO8601_FMT)));
+ outputstr = text_to_cstring(jsontext);
+ appendStringInfoString(result, outputstr);
+ pfree(outputstr);
+ pfree(jsontext);
+ break;
case JSONTYPE_JSON:
/* JSON and JSONB output will already be escaped */
outputstr = OidOutputFunctionCall(outfuncoid, val);
diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out
index 9f08676..c4dc8b0 100644
--- a/src/test/regress/expected/json.out
+++ b/src/test/regress/expected/json.out
@@ -403,6 +403,29 @@ SELECT row_to_json(row((select array_agg(x) as d from generate_series(5,10) x)),
{"f1":[5,6,7,8,9,10]}
(1 row)
+-- to_json, timestamps
+select to_json(timestamp '2014-05-28 12:22:35.614298');
+ to_json
+------------------------------
+ "2014-05-28T12:22:35.614298"
+(1 row)
+
+BEGIN;
+SET LOCAL TIME ZONE 10.5;
+select to_json(timestamptz '2014-05-28 12:22:35.614298-04');
+ to_json
+------------------------------------
+ "2014-05-29T02:52:35.614298+10:30"
+(1 row)
+
+SET LOCAL TIME ZONE -8;
+select to_json(timestamptz '2014-05-28 12:22:35.614298-04');
+ to_json
+---------------------------------
+ "2014-05-28T08:22:35.614298-08"
+(1 row)
+
+COMMIT;
--json_agg
SELECT json_agg(q)
FROM ( SELECT $$a$$ || x AS b, y AS c,
diff --git a/src/test/regress/expected/json_1.out b/src/test/regress/expected/json_1.out
index 13f7687..629e98e 100644
--- a/src/test/regress/expected/json_1.out
+++ b/src/test/regress/expected/json_1.out
@@ -403,6 +403,29 @@ SELECT row_to_json(row((select array_agg(x) as d from generate_series(5,10) x)),
{"f1":[5,6,7,8,9,10]}
(1 row)
+-- to_json, timestamps
+select to_json(timestamp '2014-05-28 12:22:35.614298');
+ to_json
+------------------------------
+ "2014-05-28T12:22:35.614298"
+(1 row)
+
+BEGIN;
+SET LOCAL TIME ZONE 10.5;
+select to_json(timestamptz '2014-05-28 12:22:35.614298-04');
+ to_json
+------------------------------------
+ "2014-05-29T02:52:35.614298+10:30"
+(1 row)
+
+SET LOCAL TIME ZONE -8;
+select to_json(timestamptz '2014-05-28 12:22:35.614298-04');
+ to_json
+---------------------------------
+ "2014-05-28T08:22:35.614298-08"
+(1 row)
+
+COMMIT;
--json_agg
SELECT json_agg(q)
FROM ( SELECT $$a$$ || x AS b, y AS c,
diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql
index 2ae5b82..6c2faec 100644
--- a/src/test/regress/sql/json.sql
+++ b/src/test/regress/sql/json.sql
@@ -100,6 +100,17 @@ FROM rows q;
SELECT row_to_json(row((select array_agg(x) as d from generate_series(5,10) x)),false);
+-- to_json, timestamps
+
+select to_json(timestamp '2014-05-28 12:22:35.614298');
+
+BEGIN;
+SET LOCAL TIME ZONE 10.5;
+select to_json(timestamptz '2014-05-28 12:22:35.614298-04');
+SET LOCAL TIME ZONE -8;
+select to_json(timestamptz '2014-05-28 12:22:35.614298-04');
+COMMIT;
+
--json_agg
SELECT json_agg(q)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
