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

Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx> · Sat, 21 Dec 2013 15:42:28 +1300



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

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

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