Thread: BUG #12578: row_to_json() and to_json() add 'T' in timestamp field.
The following bug has been logged on the website: Bug reference: 12578 Logged by: Yoong Hor Meng Email address: yoonghm@gmail.com PostgreSQL version: 9.4.0 Operating system: Linux Ubuntu Description: I built the binary manually instead of apt-get instal row_to_json and to_json replace the space between date and time. However a post in http://www.postgresql.org/message-id/CACfv+pLDzZji5C3iS=arBmq074Yi3Ez-+g8pzYF+Qr0dwU=cnQ@mail.gmail.com shows that other wanted 'T' separater in the date-time string. To simulate the problem with to_json(): postgres=# select to_json(now()); to_json ----------------------------------- "2015-01-17T17:35:19.47211+08:00" (1 row) postgres=# select row_to_json(row(now())); row_to_json {"f1":"2015-01-17T17:52:57.387618+08:00"} (1 row)
yoonghm@gmail.com writes:
> row_to_json and to_json replace the space between date and time.
This is not a bug; it's an intentional behavioral change. Per the
third bullet point in the 9.4 release notes:
* When converting values of type date, timestamp or timestamptz to JSON,
render the values in a format compliant with ISO 8601 (Andrew Dunstan)
Previously such values were rendered according to the current DateStyle
setting; but many JSON processors require timestamps to be in ISO 8601
format. If necessary, the previous behavior can be obtained by
explicitly casting the datetime value to text before passing it to the
JSON conversion function.
regards, tom lane
Thanks for the insights.
I have submitted a documentation comment on PostgreSQL 9.4 document,
Section 9.6, to add in examples on
to_json() and row_to_json() using now() to illustrate ISO 8601. For
examples:
postgres=# select to_json(now());
to_json
------------------------------------
"2015-01-18T01:24:24.488214+08:00"
(1 row)
postgres=# select to_json(now()::TEXT);
to_json
---------------------------------
"2015-01-18 01:24:36.881821+08"
(1 row)
ems=# select row_to_json(row(now()));
row_to_json
-------------------------------------------
{"f1":"2015-01-18T01:26:28.082628+08:00"}
(1 row)
ems=# select row_to_json(row(now()::TEXT));
row_to_json
----------------------------------------
{"f1":"2015-01-18 01:26:40.867813+08"}
(1 row)
On Sat, Jan 17, 2015 at 11:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> yoonghm@gmail.com writes:
> > row_to_json and to_json replace the space between date and time.
>
> This is not a bug; it's an intentional behavioral change. Per the
> third bullet point in the 9.4 release notes:
>
> * When converting values of type date, timestamp or timestamptz to JSON,
> render the values in a format compliant with ISO 8601 (Andrew Dunstan)
>
> Previously such values were rendered according to the current DateStyle
> setting; but many JSON processors require timestamps to be in ISO 8601
> format. If necessary, the previous behavior can be obtained by
> explicitly casting the datetime value to text before passing it to the
> JSON conversion function.
>
>
> regards, tom lane
>
Hor Meng Yoong wrote > Thanks for the insights. > > I have submitted a documentation comment on PostgreSQL 9.4 document, > Section 9.6, to add in examples on > to_json() and row_to_json() using now() to illustrate ISO 8601. For > examples: What does this have to do with bit strings? A note in 9.15 indicating the change in behavior and the means to get back to the old behavior might be warranted but otherwise the current behavior is correct and what people starting from scratch would likely expect so noting it anywhere except the change log is not a strong necessity. Adding four examples doesn't impress me if the only goal is to show this behavior change. -- View this message in context: http://postgresql.nabble.com/BUG-12578-row-to-json-and-to-json-add-T-in-timestamp-field-tp5834396p5834421.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.