[GENERAL] Timstamp to Json conversion issue

2015-01-13 Thread Yelai, Ramkumar IN BLR STS 
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

Re: [GENERAL] Timstamp to Json conversion issue

2015-01-13 Thread Adrian Klaver 

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

Re: [GENERAL] Timstamp to Json conversion issue

2015-01-13 Thread Tom Lane 
Yelai, Ramkumar IN BLR STS ramkumar.ye...@siemens.com 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