Thread: 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.... > > > -- > 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
>>
>> --
>> 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 > > -- > 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 > > 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.