Re: [GENERAL] Timstamp to Json conversion issue
"Yelai, Ramkumar IN BLR STS" writes: > I have migrated my postgresql 9.2 schemas to Postgres 9.4 (only table scheme > migrated not the data ). I have used the following sql to convert table > output to json in 9.2. > select array_to_json(array_agg(row_to_json(R.*)))::text from ( select " ID", > " TIME" from "SN_TestTable" )R; > IN 9.2, I used to get this result "[{"id":1,"time":"2015-01-13 > 12:09:45.348"}]" > But same code in 9.4 produce this result > "[{"id":1,"time":"2015-01-13T12:09:45.348"}]" . "T" separator is added > between date and time. This is an intentional change. As far as we know, any JSON processor that reads timestamps should be happy with the "T", because that syntax is required by the JSON RFC. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timstamp to Json conversion issue
On 01/12/2015 10:45 PM, Yelai, Ramkumar IN BLR STS wrote: Hi I have migrated my postgresql 9.2 schemas to Postgres 9.4 (only table scheme migrated not the data ). I have used the following sql to convert table output to json in 9.2. select array_to_json(array_agg(row_to_json(R.*)))::text from ( select " ID", " TIME” from "SN_TestTable" )R; IN 9.2, I used to get this result "[{"id":1,"time":"2015-01-13 12:09:45.348"}]" But same code in 9.4 produce this result "[{"id":1,"time":"2015-01-13T12:09:45.348"}]" . “T” separator is added between date and time. Seems json coversion is followed ISO8601 for the timestamp. This issue is resolved by sending “TIME” column as text instead of Timestamp without timezone. But how do I fix this problem without converting to text. You have already found the fix:) It is a change in 9.4: http://www.postgresql.org/docs/9.4/interactive/release-9-4.html 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. With best regards, Ramkumar Yelai Siemens Technology and Services Private Limited CT DC AA I HOUSE DEV GL4 84, Hosur Road Bengaluru 560100, Indien Tel.: +91 80 33136494 Fax: +91 80 33133389 Mobil: +91 9886182031 _mailto:ramkumar.yelai@siemens.com_ _http://www.siemens.co.in/STS_ Registered Office: 130, Pandurang Budhkar Marg, Worli, Mumbai 400 018. Telephone +91 22 39677000. Fax +91 22 39677075. Other Offices: Bangalore, Chennai, Gurgaon, Noida, Kolkata, Pune. Corporate Identity number:U9MH1986PLC093854 -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Timstamp to Json conversion issue
Hi I have migrated my postgresql 9.2 schemas to Postgres 9.4 (only table scheme migrated not the data ). I have used the following sql to convert table output to json in 9.2. select array_to_json(array_agg(row_to_json(R.*)))::text from ( select " ID", " TIME" from "SN_TestTable" )R; IN 9.2, I used to get this result "[{"id":1,"time":"2015-01-13 12:09:45.348"}]" But same code in 9.4 produce this result "[{"id":1,"time":"2015-01-13T12:09:45.348"}]" . "T" separator is added between date and time. Seems json coversion is followed ISO8601 for the timestamp. This issue is resolved by sending "TIME" column as text instead of Timestamp without timezone. But how do I fix this problem without converting to text. With best regards, Ramkumar Yelai Siemens Technology and Services Private Limited CT DC AA I HOUSE DEV GL4 84, Hosur Road Bengaluru 560100, Indien Tel.: +91 80 33136494 Fax: +91 80 33133389 Mobil: +91 9886182031 mailto:ramkumar.ye...@siemens.com http://www.siemens.co.in/STS Registered Office: 130, Pandurang Budhkar Marg, Worli, Mumbai 400 018. Telephone +91 22 39677000. Fax +91 22 39677075. Other Offices: Bangalore, Chennai, Gurgaon, Noida, Kolkata, Pune. Corporate Identity number:U9MH1986PLC093854