Thread: ISO8601 nitpicking
As it turns out, evidence would suggests that the "ISO" output in Postgres isn't, unless there's an ISO standard for date and time that is referring to other than 8601. It does not permit use of a space between the date and the time, as seen in: SELECT now(); now -------------------------------2012-02-23 23:31:59.580915-08 (1 row) Thanks to Vasily Chekalkin for digging that up. He was annoyed at the time, so someone actually cares now and again, sample size one. It is true that many common adaptations of ISO8601 do allow the space, and many pages on the web that abstract the standard include that variant, but it is not the letter of the standard, as far as I can tell. It is an acceptable letter of the standard in RFC3339. Unless one actually digs down in the ISO document -- this is the third edition(!) -- one may be misinformed. Or perhaps it's buried in the standard's PDF. Here's a link to the standard: http://dotat.at/tmp/ISO_8601-2004_E.pdf I'm not sure if there's anything to be done here other than a mention of errata in the manual. Alternatively, datestyle = 'sql' and datestyle = 'iso' may be reasonably different, after all. -- fdr
On tor, 2012-02-23 at 23:41 -0800, Daniel Farina wrote: > As it turns out, evidence would suggests that the "ISO" output in > Postgres isn't, unless there's an ISO standard for date and time that > is referring to other than 8601. Yes, ISO 9075, the SQL standard. This particular issue has been discussed many times; see the archives.
On Fri, Feb 24, 2012 at 4:45 AM, Peter Eisentraut <peter_e@gmx.net> wrote: > On tor, 2012-02-23 at 23:41 -0800, Daniel Farina wrote: >> As it turns out, evidence would suggests that the "ISO" output in >> Postgres isn't, unless there's an ISO standard for date and time that >> is referring to other than 8601. > > Yes, ISO 9075, the SQL standard. This particular issue has been > discussed many times; see the archives. > I did try searching, but this did not come up quickly, except as "the T is not necessary," as is commonly repeated on the web. The manual is misleading to me on this admittedly very fine point: """ 8.5.2. Date/Time Output The output format of the date/time types can be set to one of the four styles ISO 8601, SQL (Ingres), traditional POSTGRES (Unix date format), or German. The default is the ISO format. (The SQL standard requires the use of the ISO 8601 format. The name of the "SQL" output format is a historical accident.) Table 8-14 shows examples of each output style. The output of the date and time types is of course only the date or time part in accordance with the given examples. Table 8-14. Date/Time Output Styles Style Specification Description Example ISO ISO 8601/SQL standard 1997-12-17 07:37:16-08 """ If the SQL standard *also*, in fact, refers to ISO 8601 (rather than defining a format or extension in in ISO 9075), then sans picking a tiny detail out of the standard I think my point still stands. I would appreciate an archive link that hashes this out, if it has been sifted down into oblivion by search technology. -- fdr
On fre, 2012-02-24 at 10:40 -0800, Daniel Farina wrote: > On Fri, Feb 24, 2012 at 4:45 AM, Peter Eisentraut <peter_e@gmx.net> wrote: > > On tor, 2012-02-23 at 23:41 -0800, Daniel Farina wrote: > >> As it turns out, evidence would suggests that the "ISO" output in > >> Postgres isn't, unless there's an ISO standard for date and time that > >> is referring to other than 8601. > > > > Yes, ISO 9075, the SQL standard. This particular issue has been > > discussed many times; see the archives. > > > > I did try searching, but this did not come up quickly, except as "the > T is not necessary," as is commonly repeated on the web. This thread for example: http://archives.postgresql.org/message-id/EC26F5CE-9F3B-40C9-BF23-F0C2B96E388C@gmail.com > The manual is misleading to me on this admittedly very fine point: Yes, that should probably be cleaned up. I repeat my contribution to the above thread: So we'd have a setting called "ECMA" that's really ISO, and a setting called "ISO" that's really SQL, and asetting called "SQL" that's really Postgres, and a setting called "Postgres" that's also Postgres but different. Maybe we should just rename the setings to A, B, C, and D.