to_json(now()) result doesn't have 'T' separator

On Fri, Dec 20, 2013 at 4:18 PM, Joe Van Dyk <joe@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.

On Fri, Dec 20, 2013 at 4:24 PM, Joe Van Dyk <joe@tanga.com> wrote:

On Fri, Dec 20, 2013 at 4:18 PM, Joe Van Dyk <joe@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

On 21/12/13 15:27, Joe Van Dyk wrote:

On Fri, Dec 20, 2013 at 4:24 PM, Joe Van Dyk <joe@tanga.com
<mailto:joe@tanga.com>> wrote:

On Fri, Dec 20, 2013 at 4:18 PM, Joe Van Dyk <joe@tanga.com
<mailto:joe@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_representations
says '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

Should there be a boolean option "isTeaTime" - for inserting a "T' in
the output?

Cheers,
Gavin

This has been brought up a few times in the past:
/messages/by-id/CAAZKuFZF5=raA=RLncqEg_8GsJ9vi4_E-fi1aOmK4zP+dxcx4g@mail.gmail.com
/messages/by-id/EC26F5CE-9F3B-40C9-BF23-F0C2B96E388C@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 <joe@tanga.com> wrote:

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 <joe@tanga.com> wrote:

On Mon, Dec 23, 2013 at 5:37 PM, Joe Van Dyk <joe@tanga.com> wrote:

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"}

Close as I could get using above is:

test=# select to_json(created_at) from t;
to_json
------------------------------------
"2013-12-23T19:09:33.886092-08:00"

Seems row_to_json bypasses casts whereas to_json does not.
--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general