Re: BUG #4115: PostgreSQL ISO format is not really ISO

Daniel Ruoso wrote:
> On the other hand, some important ISO8601-based specifications only
> accept the dateTtime notation, for instance XML Schema.
>
> As I was talking on #postgresql, I think it would be nice to have that
> output option as one of the date/time output styles (name it ISO8601 or
> ISO-strict), and it really doesn't need to be the default (the way pg
> uses it now is nice for the human reader).

The fact that you can alter the date/time output format at all is really quite
a legacy feature, and its use should be frowned upon in contemporary
applications. Although we don't declare this anwhere, the "ISO" should be
read to refer to ISO 9075, which is the SQL standard, and that specifies
exactly the format we use. All the other formats are a transitional measure
from legacy behavior. Using them nowadays will make your application
incompatible with the SQL standard and it will break various client drivers.

If you need a different output format, use a formatting function or some other
post-output formatting mechanism. As a note, the SQL-to-XML Schema coverter
uses the correct format that you are looking for:

peter=> SELECT query_to_xml('select localtimestamp', false, false, '');
query_to_xml
---------------------------------------------------------------
<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<row>
<timestamp>2008-04-20T17:26:21.187424</timestamp>
</row>

</table>