xml queries & date format
i am using the xml add-ons, but the date output format seems to be wrong :
i have
show datestyle;
DateStyle
-----------
SQL, DMY
select agenda_datum from dossiers where id = 61;
agenda_datum
--------------
29/07/2008
select table_to_xml('dossiers', false, false, '');
gives (knip )
<row>
<id>62</id>
<voorwerp_detail>5 coils 5.622 kg</voorwerp_detail>
<schade_datum>2008-07-29</schade_datum>
<voorbehoud>false</voorbehoud>
<protest>false</protest>
<vordering>false</vordering>
<afgewezen>false</afgewezen>
<gedeeltelijk_afgewezen>false</gedeeltelijk_afgewezen>
<verhaal>false</verhaal>
<administratief>false</administratief>
</row>
jef
Jef Peeraer <jef.peeraer@telenet.be> writes:
i am using the xml add-ons, but the date output format seems to be wrong :
I think the conversion to xml intentionally always uses ISO date format,
because that's required by some spec somewhere.
regards, tom lane
Tom Lane wrote:
Jef Peeraer <jef.peeraer@telenet.be> writes:
i am using the xml add-ons, but the date output format seems to be wrong :
I think the conversion to xml intentionally always uses ISO date format,
because that's required by some spec somewhere.
Yes, it follows XML Schema. Which is why the output format is even
slightly different from the SQL-mandated ISO format.
On Thu, 11 Sep 2008, Peter Eisentraut wrote:
Tom Lane wrote:
Jef Peeraer <jef.peeraer@telenet.be> writes:
i am using the xml add-ons, but the date output format seems to be wrong :
I think the conversion to xml intentionally always uses ISO date format,
because that's required by some spec somewhere.Yes, it follows XML Schema. Which is why the output format is even slightly
different from the SQL-mandated ISO format.
i understand, but that makes it very difficult to change the date format
afterwards. i simple flag to indicate no date conversion would be
helpfull....
Show quoted text
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2008/9/11 Jef Peeraer <jef.peeraer@telenet.be>:
On Thu, 11 Sep 2008, Peter Eisentraut wrote:
Tom Lane wrote:
Jef Peeraer <jef.peeraer@telenet.be> writes:
i am using the xml add-ons, but the date output format seems to be wrong :
I think the conversion to xml intentionally always uses ISO date format,
because that's required by some spec somewhere.Yes, it follows XML Schema. Which is why the output format is even slightly
different from the SQL-mandated ISO format.i understand, but that makes it very difficult to change the date format
afterwards. i simple flag to indicate no date conversion would be
helpfull....
no, use explicit casting to varchar
-- xml formating style
postgres=# select xmlforest(current_timestamp as date);
xmlforest
-----------------------------------------------
<date>2008-09-11T12:21:44.600512+02:00</date>
(1 row)
postgres=# select xmlforest(current_timestamp::text as date);
xmlforest
--------------------------------------------
<date>2008-09-11 12:22:25.180611+02</date>
(1 row)
postgres=# set datestyle to German ;
SET
postgres=# select xmlforest(current_timestamp::text as date);
xmlforest
----------------------------------------------
<date>11.09.2008 12:22:32.947672 CEST</date>
(1 row)
regards
Pavel Stehule
Show quoted text
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Jef Peeraer wrote:
On Thu, 11 Sep 2008, Peter Eisentraut wrote:
Tom Lane wrote:
Jef Peeraer <jef.peeraer@telenet.be> writes:
i am using the xml add-ons, but the date output format seems to be wrong :
I think the conversion to xml intentionally always uses ISO date format,
because that's required by some spec somewhere.Yes, it follows XML Schema. Which is why the output format is even slightly
different from the SQL-mandated ISO format.i understand, but that makes it very difficult to change the date format
afterwards. i simple flag to indicate no date conversion would be
helpfull....
Well, these table_to_xml etc. functions are heavily constrained by the
SQL standard, XML Schema, and others. They do what they are supposed to
do. You are free to design your own XML export format or apply
postprocessing to the existing ones (XSLT?). I don't think we should
overload the existing functions with everyone's favorite but apparently
completely nonstandard formatting variant flag.
On Thu, 11 Sep 2008, Peter Eisentraut wrote:
Jef Peeraer wrote:
On Thu, 11 Sep 2008, Peter Eisentraut wrote:
Tom Lane wrote:
Jef Peeraer <jef.peeraer@telenet.be> writes:
i am using the xml add-ons, but the date output format seems to be
wrong :I think the conversion to xml intentionally always uses ISO date format,
because that's required by some spec somewhere.Yes, it follows XML Schema. Which is why the output format is even
slightly
different from the SQL-mandated ISO format.i understand, but that makes it very difficult to change the date format
afterwards. i simple flag to indicate no date conversion would be
helpfull....Well, these table_to_xml etc. functions are heavily constrained by the SQL
standard, XML Schema, and others. They do what they are supposed to do. You
are free to design your own XML export format or apply postprocessing to the
existing ones (XSLT?). I don't think we should overload the existing
functions with everyone's favorite but apparently completely nonstandard
formatting variant flag.
it would be a flag to indicate no conversion from the datestyle settings
in the database...i think, from a users perspective, the table_to_xml is
completely useless, if you have to reformat everything afterwards....
jef
Show quoted text
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2008/9/11 Jef Peeraer <jef.peeraer@telenet.be>:
On Thu, 11 Sep 2008, Peter Eisentraut wrote:
Jef Peeraer wrote:
On Thu, 11 Sep 2008, Peter Eisentraut wrote:
Tom Lane wrote:
Jef Peeraer <jef.peeraer@telenet.be> writes:
i am using the xml add-ons, but the date output format seems to be
wrong :I think the conversion to xml intentionally always uses ISO date format,
because that's required by some spec somewhere.Yes, it follows XML Schema. Which is why the output format is even
slightly
different from the SQL-mandated ISO format.i understand, but that makes it very difficult to change the date format
afterwards. i simple flag to indicate no date conversion would be
helpfull....Well, these table_to_xml etc. functions are heavily constrained by the SQL
standard, XML Schema, and others. They do what they are supposed to do. You
are free to design your own XML export format or apply postprocessing to the
existing ones (XSLT?). I don't think we should overload the existing
functions with everyone's favorite but apparently completely nonstandard
formatting variant flag.it would be a flag to indicate no conversion from the datestyle settings
in the database...i think, from a users perspective, the table_to_xml is
completely useless, if you have to reformat everything afterwards....
I am not sure - mostly people should generate "valid" xml file.
Sending invalid dates in XML is wrong.
Pavel
Show quoted text
jef
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Jef Peeraer wrote:
it would be a flag to indicate no conversion from the datestyle settings
in the database...i think, from a users perspective, the table_to_xml is
completely useless, if you have to reformat everything afterwards....
Just write a function that does your formatting afterwards. You can
even name it table_to_xml.