correct format for date, time, timestamp for XML functionality

"Pavel Stehule" <pavel.stehule@hotmail.com> writes:

this patch ensures independency datetime fields on current datestyle
setting. Add new internal datestyle USE_XSD_DATESTYLE. It's almoust same to
USE_ISO_DATESTYLE. Differences are for timestamp:

ISO: yyyy-mm-dd hh24:mi:ss
XSD: yyyy-mm-ddThh24:mi:ss

Why is that a good idea? Even if some standard out there mandates the
'T', I'd bet lunch that the other format is a whole lot more portable.

regards, tom lane

"Pavel Stehule" <pavel.stehule@hotmail.com> writes:

this patch ensures independency datetime fields on current datestyle
setting. Add new internal datestyle USE_XSD_DATESTYLE. It's almoust same

to

USE_ISO_DATESTYLE. Differences are for timestamp:

ISO: yyyy-mm-dd hh24:mi:ss
XSD: yyyy-mm-ddThh24:mi:ss

Why is that a good idea? Even if some standard out there mandates the
'T', I'd bet lunch that the other format is a whole lot more portable.

if you use xsd schema then situation is clear. I have to respect it, because
xsd:datetime is well defined and SQL/XML expect respecting xsd. If I don't
use xsd schema, then I teoreticly can put date in any format. Sample: we use
german format, but everybody with good mind doesn't use it for xml, because
then he cannot use xml validation based on xsd schema.

xsd format use Oracle 10g:
http://forums.oracle.com/forums/thread.jspa?threadID=467278&amp;tstart=0
9x used ISO format, which is (however) invalid.

http://books.xmlschemata.org/relaxng/ch19-77049.html

Currently without this patch PostgreSQL generate invalalid xml documents.
That is all.

My patch doesn't protect any output. Simply use cast to text, or to_char
fce.

nice a day
Pavel Stehule

_________________________________________________________________
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/

Pavel Stehule wrote:

Hello,

this patch ensures independency datetime fields on current datestyle
setting. Add new internal datestyle USE_XSD_DATESTYLE. It's almoust
same to USE_ISO_DATESTYLE. Differences are for timestamp:

ISO: yyyy-mm-dd hh24:mi:ss
XSD: yyyy-mm-ddThh24:mi:ss

Pavel,

I agree that we should have some support for XSD date style so that we
can produce validatable XML documents. In fact I had to make just such a
transformation on data pulled from Postgres recently in application code
to get a document to validate.

However, I have not seen this topic discussed on -hackers. The way we
work is that ideas about features should be discussed there before you
submit a patch. For one thing, -hackers has a somewhat wider set of
readers than -patches. Also, you might well get good ideas about any
likely difficulties. Just lobbing a patch for an undiscussed feature
over the wall like this is not good practice. You should get signoff on
the idea before you start coding, even for fairly small changes.

cheers

andrew

Pavel Stehule wrote:

Hello,

this patch ensures independency datetime fields on current datestyle
setting. Add new internal datestyle USE_XSD_DATESTYLE. It's almoust same
to USE_ISO_DATESTYLE. Differences are for timestamp:

ISO: yyyy-mm-dd hh24:mi:ss
XSD: yyyy-mm-ddThh24:mi:ss

Pavel,

I agree that we should have some support for XSD date style so that we can
produce validatable XML documents. In fact I had to make just such a
transformation on data pulled from Postgres recently in application code to
get a document to validate.

However, I have not seen this topic discussed on -hackers. The way we work
is that ideas about features should be discussed there before you submit a
patch. For one thing, -hackers has a somewhat wider set of readers than
-patches. Also, you might well get good ideas about any likely
difficulties. Just lobbing a patch for an undiscussed feature over the wall
like this is not good practice. You should get signoff on the idea before
you start coding, even for fairly small changes.

I am sorry. I reported this two times before. This patch is related only for
xml functionality. XSD datestyle is only one internal constant. There are no
new datestyle (I hope so can be usefull). My patch is small bug fix like
Peter's patch for boolean datatype. Generating invalid xml is bug not
feature, no?

Primary I had to send this patch to Peter.

nice a day
Pavel Stehule

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

Pavel Stehule wrote:

Pavel Stehule wrote:

Hello,

this patch ensures independency datetime fields on current datestyle
setting. Add new internal datestyle USE_XSD_DATESTYLE. It's almoust
same to USE_ISO_DATESTYLE. Differences are for timestamp:

ISO: yyyy-mm-dd hh24:mi:ss
XSD: yyyy-mm-ddThh24:mi:ss

Pavel,

I agree that we should have some support for XSD date style so that
we can produce validatable XML documents. In fact I had to make just
such a transformation on data pulled from Postgres recently in
application code to get a document to validate.

However, I have not seen this topic discussed on -hackers. The way we
work is that ideas about features should be discussed there before
you submit a patch. For one thing, -hackers has a somewhat wider set
of readers than -patches. Also, you might well get good ideas about
any likely difficulties. Just lobbing a patch for an undiscussed
feature over the wall like this is not good practice. You should get
signoff on the idea before you start coding, even for fairly small
changes.

I am sorry. I reported this two times before. This patch is related
only for xml functionality. XSD datestyle is only one internal
constant. There are no new datestyle (I hope so can be usefull). My
patch is small bug fix like Peter's patch for boolean datatype.
Generating invalid xml is bug not feature, no?

Primary I had to send this patch to Peter.

I'm not sure that we are actually guaranteeing anything about XML
validity against any schema or DTD, are we?

If there was previous email I apologise, as I didn't find it when I
looked. Perhaps in such cases you could include a ref to the archive URL.

cheers

andrew

On Tue, Feb 20, 2007 at 04:32:28PM +0100, Pavel Stehule wrote:

I am sorry. I reported this two times before. This patch is related only
for xml functionality. XSD datestyle is only one internal constant. There
are no new datestyle (I hope so can be usefull). My patch is small bug fix
like Peter's patch for boolean datatype. Generating invalid xml is bug not
feature, no?

If it's just for XSD, perhaps it should be a part of the XML output
functionality instead of being a "global" datestyle?

//Magnus

I'm not sure that we are actually guaranteeing anything about XML validity
against any schema or DTD, are we?

what?

ofcourse you cannot garant validity against any schema. But mapping
functions are standardised and expect xsd. And I what I can meet protocols
based on xml, they respect xsd everywhere.

I repeat. When XML functions don't produce XML schema, then all is possible.
But using xsd standard is safe way (like ISO format in SQL world). With
nested XML schema whitch is related to xsd there isn't any different way.

The best solution is validation XML before output to client. pg have to
produce everytime valid xml

If there was previous email I apologise, as I didn't find it when I looked.
Perhaps in such cases you could include a ref to the archive URL.

cheers

andrew

_________________________________________________________________
With tax season right around the corner, make sure to follow these few
simple tips.
http://articles.moneycentral.msn.com/Taxes/PreparationTips/PreparationTips.aspx?icid=HMFebtagline

On Tue, Feb 20, 2007 at 04:32:28PM +0100, Pavel Stehule wrote:

I am sorry. I reported this two times before. This patch is related only
for xml functionality. XSD datestyle is only one internal constant.

There

are no new datestyle (I hope so can be usefull). My patch is small bug

fix

like Peter's patch for boolean datatype. Generating invalid xml is bug

not

feature, no?

If it's just for XSD, perhaps it should be a part of the XML output
functionality instead of being a "global" datestyle?

I share code and I needed one safe enum value. That's all. There isn't new
global datestyle. I didn't want to duplicate code from timestamp.c and
date.c.

Pavel

_________________________________________________________________
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/

Am Dienstag, 20. Februar 2007 16:54 schrieb Andrew Dunstan:

I'm not sure that we are actually guaranteeing anything about XML
validity against any schema or DTD, are we?

That is the "xmlschema" part of table_to_xmlschema() et al. recently
discussed. That entire functionality hinges on producing output that
validates against XML Schema schemas, and so we cannot pick the data type
formats outselves.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:

Am Dienstag, 20. Februar 2007 16:54 schrieb Andrew Dunstan:

I'm not sure that we are actually guaranteeing anything about XML
validity against any schema or DTD, are we?

That is the "xmlschema" part of table_to_xmlschema() et al. recently
discussed. That entire functionality hinges on producing output that
validates against XML Schema schemas, and so we cannot pick the data type
formats outselves.

Then why would we use a setting to govern this? Should we not simply
ensure that we always output timestamps in XML using the correct ISO8601
format?

cheers

andrew

From: Andrew Dunstan <andrew@dunslane.net>
To: Peter Eisentraut <peter_e@gmx.net>
CC: Pavel Stehule <pavel.stehule@hotmail.com>,
pgsql-patches@postgresql.org
Subject: Re: [PATCHES] correct format for date, time, timestamp for XML
functionality
Date: Tue, 20 Feb 2007 11:37:31 -0500

Peter Eisentraut wrote:

Am Dienstag, 20. Februar 2007 16:54 schrieb Andrew Dunstan:

I'm not sure that we are actually guaranteeing anything about XML
validity against any schema or DTD, are we?

That is the "xmlschema" part of table_to_xmlschema() et al. recently
discussed. That entire functionality hinges on producing output that
validates against XML Schema schemas, and so we cannot pick the data type
formats outselves.

Then why would we use a setting to govern this? Should we not simply ensure
that we always output timestamps in XML using the correct ISO8601 format?

xsd knows datetime type. You can inherit it, and then you have to respect
it. You can do own type, but you lost information, and any general scripts
don't understand. I don't know why xsd doesn't iso format, but its simply
fact. Please read this discussion:
http://forums.oracle.com/forums/thread.jspa?threadID=467278&amp;tstart=0

cheers

andrew

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/

Pavel Stehule wrote:

xsd knows datetime type. You can inherit it, and then you have to
respect it. You can do own type, but you lost information, and any
general scripts don't understand. I don't know why xsd doesn't iso
format, but its simply fact. Please read this discussion:
http://forums.oracle.com/forums/thread.jspa?threadID=467278&amp;tstart=0

Ok, I've re-read the patch, and now understand what it's doing. Sorry
for the noise.

cheers

andrew

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------

Pavel Stehule wrote:

Hello,

this patch ensures independency datetime fields on current datestyle
setting. Add new internal datestyle USE_XSD_DATESTYLE. It's almoust same to
USE_ISO_DATESTYLE. Differences are for timestamp:

ISO: yyyy-mm-dd hh24:mi:ss
XSD: yyyy-mm-ddThh24:mi:ss

I found one link about this topic:
http://forums.oracle.com/forums/thread.jspa?threadID=467278&amp;tstart=0

Regards
Pavel Stehule

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

[ Attachment, skipping... ]

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Pavel Stehule wrote:

this patch ensures independency datetime fields on current datestyle
setting. Add new internal datestyle USE_XSD_DATESTYLE. It's almoust
same to USE_ISO_DATESTYLE.

I've installed this patch and made some additional adjustments so the
time zone format matches the XSD format.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/