I tried making some cast functions as follows, but it doesn't seem to work
properly:
create or replace function iso_timestamp(timestamptz) returns json as $$
select ('"' ||
substring(xmlelement(name x, $1)::text from 4 for 32) || '"'
)::json
$$ language sql immutable;
create cast (timestamptz as json) with function iso_timestamp (timestamptz)
as implicit;
create function to_json(timestamptz) returns json as $$
select $1::json
$$ language sql immutable;
create table t (id serial primary key, created_at timestamptz default
now());
insert into t values (default);
select row_to_json(t) from t;
row_to_json
-------------------------------------------------------
{"id":1,"created_at":"2013-12-23 17:37:08.825935-08"}
On Mon, Dec 23, 2013 at 5:28 PM, Joe Van Dyk <j...@tanga.com> wrote:
> This has been brought up a few times in the past:
>
> http://www.postgresql.org/message-id/CAAZKuFZF5=raA=rlncqeg_8gsj9vi4_e-fi1aomk4zp+dxc...@mail.gmail.com
>
> http://www.postgresql.org/message-id/ec26f5ce-9f3b-40c9-bf23-f0c2b96e3...@gmail.com
>
> Any chance it could be fixed? I can't figure out a way to easily let
> javascript applications parse json timestamps generated by postgresql in
> row_to_json() statements.
>
>
> On Fri, Dec 20, 2013 at 6:27 PM, Joe Van Dyk <j...@tanga.com> wrote:
>
>> On Fri, Dec 20, 2013 at 4:24 PM, Joe Van Dyk <j...@tanga.com> wrote:
>>
>>>
>>>
>>> On Fri, Dec 20, 2013 at 4:18 PM, Joe Van Dyk <j...@tanga.com> wrote:
>>>
>>>> # select to_json(now());
>>>> to_json
>>>> ---------------------------------
>>>> "2013-12-20 15:53:39.098204-08"
>>>> (1 row)
>>>>
>>>> I'd like to see it output "2013-12-20T15:53:39.098204-08" so it's
>>>> interchangeable with more systems.
>>>>
>>>
>>> Actually, "2013-12-20T15:53:39.44553-08:00" would be ideal.
>>>
>>
>> I dug into the docs some more, and I found this at
>> http://www.postgresql.org/docs/9.3/static/datatype-datetime.html:
>> "Note: ISO 8601 specifies the use of uppercase letter T to separate the
>> date and time.PostgreSQL accepts that format on input, but on output it
>> uses a space rather than T, as shown above. This is for readability and for
>> consistency with RFC 3339 as well as some other database systems."
>>
>> So I looked up RFC 3339 (http://www.ietf.org/rfc/rfc3339.txt) and read:
>> "NOTE: ISO 8601 defines date and time separated by "T". Applications
>> using this syntax may choose, for the sake of readability, to specify a
>> full-date and full-time separated by (say) a space character."
>>
>> Doesn't seem like including the 'T' separator would be inconsistent with
>> RFC 3399?
>>
>> I'm sending the output of to_json(now()) to web browsers. Most browsers
>> aren't able to parse the date strings if they are missing the 'T'
>> separator. If datetime strings could include the 'T' time separator and the
>> full timezone, that would make generating json that worked with web
>> browsers much simpler.
>>
>> Joe
>>
>>
>>
>>>
>>>>
>>>>
>>>> http://en.wikipedia.org/wiki/ISO_8601#Combined_date_and_time_representationssays
>>>> 'T' can be omitted "by mutual agreement".
>>>>
>>>> I'm working with javascript/json systems that expect the 'T' to be
>>>> there however, so there's no mutual agreement happening.
>>>>
>>>> Thoughts? I know I can hack around it by specifying my own date format,
>>>> but I'd really like to be able to use row_to_json and other functions
>>>> without specifying custom date formats everywhere.
>>>>
>>>> Joe
>>>>
>>>
>>>
>>
>
