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

The following bug has been logged online:

Bug reference: 4115
Logged by: Daniel Ruoso
Email address: daniel(at)ruoso(dot)com
PostgreSQL version: 8.3.1
Operating system: Debian GNU/Linux lenny
Description: PostgreSQL ISO format is not really ISO
Details:

ISO8601[1] defines Date/Time ouput, and is, today, quite accepted, being the
standard used by XML Schema definitions. Which means that they have to be in
that format to be accepted by a XML validator.

The basic difference between PostgreSQL format and the ISO format is the
absence of a "T" between the date and the time.

[1] http://en.wikipedia.org/wiki/ISO_8601

On Sat, Apr 19, 2008 at 6:38 AM, Daniel Ruoso <daniel(at)ruoso(dot)com> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 4115
> Logged by: Daniel Ruoso
> Email address: daniel(at)ruoso(dot)com
> PostgreSQL version: 8.3.1
> Operating system: Debian GNU/Linux lenny
> Description: PostgreSQL ISO format is not really ISO
> Details:
>
> ISO8601[1] defines Date/Time ouput, and is, today, quite accepted, being the
> standard used by XML Schema definitions. Which means that they have to be in
> that format to be accepted by a XML validator.
>
> The basic difference between PostgreSQL format and the ISO format is the
> absence of a "T" between the date and the time.
>
> [1] http://en.wikipedia.org/wiki/ISO_8601
>

This says that a space between date and time is acceptable, although
not considered a single field.

http://en.wikipedia.org/wiki/ISO_8601#Combined_date_and_time_representations
"""
Unlike the previous examples, "2007-04-05 14:30" is considered two
separate, but acceptable, representations—one for date and the other
for time. It is then left to the reader to interpret the two separate
representations as meaning a single time point based on the context.
"""

--
regards,
Jaime Casanova
Soporte de PostgreSQL
Guayaquil - Ecuador
Cel. 087171157

Sáb, 2008-04-19 às 12:10 -0500, Jaime Casanova escreveu:
> """
> Unlike the previous examples, "2007-04-05 14:30" is considered two
> separate, but acceptable, representations—one for date and the other
> for time. It is then left to the reader to interpret the two separate
> representations as meaning a single time point based on the context.
> """

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

daniel

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>