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