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
/messages/by-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)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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:
Show quoted text
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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs