| Author |
Message
|
| missing_link |
 Posted: Wed Feb 25, 2009 7:03 am Post subject: timezone problem with xsd:dateTime |
 |
|
Acolyte
Joined: 08 Jan 2004
Posts: 60
|
Hi All
I'm calling a webservice from the broker which is returning some fields as xsd:dateTime e.g. 2009-02-25T09:49:32.000-05:00.
I need to persist in a database (oracle) character field. From my interpretation of the message broker docs I should be using the format pattern 'I' on the cast e.g. CAST(msgRef.NS1:timestamp AS CHARACTER FORMAT 'I'), however the timezone value is being dropped on insert and replaced with +00:00. I've messaed around with a few other cast formats but end up with either the TZ dropped completely or set to +00:00.
Anyone have any ideas as to what is wrong with my cast, or what I need to do to keep the source timezone?
thanks
ash. |
|
| Back to top |
|
 |
| Gaya3 |
| Posted: Wed Feb 25, 2009 7:16 am Post subject: Re: timezone problem with xsd:dateTime |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
| missing_link wrote: |
I'm calling a webservice from the broker which is returning some fields as xsd:dateTime e.g. 2009-02-25T09:49:32.000-05:00.
|
this is correct, this is XML DATE TIME Stamp
for inserting in to Oracle Data Base, convert the above DateTime to Oracle Date Time Stamp
Use the Date Time format for doing this.
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Wed Feb 25, 2009 12:18 pm Post subject: Re: timezone problem with xsd:dateTime |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| missing_link wrote: |
I'm calling a webservice from the broker which is returning some fields as xsd:dateTime e.g. 2009-02-25T09:49:32.000-05:00.
I need to persist in a database (oracle) character field. From my interpretation of the message broker docs I should be using the format pattern 'I' on the cast e.g. CAST(msgRef.NS1:timestamp AS CHARACTER FORMAT 'I'), however the timezone value is being dropped on insert and replaced with +00:00. |
When the timezone is changed from '-05:00 ' to '+00:00', what happens to the hours?
Assuming there's a message set, adjusting some of the DateTime settings in the XML wire format message set properties might help. |
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Feb 25, 2009 3:47 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| Assuming there's a message set, adjusting some of the DateTime settings in the XML wire format message set properties might help. |
Only if the domain is MRM. SOAP and XMLNSC do not use the XML physical format at all. |
|
| Back to top |
|
|
| missing_link |
| Posted: Thu Feb 26, 2009 1:16 am Post subject: |
|
|
Acolyte
Joined: 08 Jan 2004
Posts: 60
|
thanks for the replies.
the domain is SOAP as its coming back from a web service via the soap request node (configured by vendor supplied wsdl).
the hours stay the same i.e. 2009-02-25T09:49:32.000-05:00 becomes 2009-02-25T09:49:32.000+00:00. I wouldn't mind so much if the TZ got added into the time!  although i'd prefer to keep it as supplied.
if i route the message out to queue then the datetime fields appear as expected. via usertrace its showing as :time = TIMESTAMP '2009-02-25 09:26:40' (but I guess thats caused by whatever processing the trace node needs to do).
btw, running broker 6101 on solaris.
thanks
ash. |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Feb 26, 2009 2:32 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| if i route the message out to queue then the datetime fields appear as expected. via usertrace its showing as :time = TIMESTAMP '2009-02-25 09:26:40' (but I guess thats caused by whatever processing the trace node needs to do). |
Are you saying that the input field is '2009-02-25T09:49:32.000-05:00' and it displays in user trace as 'TIMESTAMP '2009-02-25 09:26:40'? If so, then that sounds like a defect. By design, the SOAP and XMLNSC parsers remove the time zone and put the GMT timestamp into the message tree. The timezone offset is stored internally and re-applied if the message is written out to a queue/other transport.
In your case, I would have expected to see 'TIMESTAMP '2009-02-25 14:26:40' in the message tree.
As a workaround, you could
- open the message definition file
- find the element/attribute which contains this date value
- change its type from xs:dateTime to xs:string
- modify your message flow to CAST to TIMESTAMP in all places where you need to process the date value as a TIMESTAMP
The last item in that list probably sounds like a lot of work. But in many cases, the date value is always treated as a string anyway, so the impact is very small. |
|
| Back to top |
|
|
| missing_link |
| Posted: Thu Feb 26, 2009 3:11 am Post subject: |
|
|
Acolyte
Joined: 08 Jan 2004
Posts: 60
|
In my unadulterated output message i have the xml:
<ns:timestamp>2009-02-26T06:00:25-05:00</ns:timestamp>
in my trace i have:
timestamp = TIMESTAMP '2009-02-26 06:00:25'
i had considered changing the xsd:dateTime's to a string, but was hoping to avoid it...but it seems like i may have no choice. its not a huge amount of work, its only changing a handful of fields, but still a pain.
if as you say the timezone offset is stored internally, then does that mean i can't do any time calculations where the input is an xsd:dateTime? I'd always need to change them to a string?
thanks
ash. |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Feb 26, 2009 3:29 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
In my unadulterated output message i have the xml:
<ns:timestamp>2009-02-26T06:00:25-05:00</ns:timestamp>
in my trace i have:
timestamp = TIMESTAMP '2009-02-26 06:00:25' |
That looks wrong to me. I would have expected 'TIMESTAMP '2009-02-26 11:00:25'. You might want to consider updating to the latest fix pack, although I'm not aware of any specific fixes to date/time handling in XMLNSC. |
|
| Back to top |
|
|
| missing_link |
| Posted: Fri Feb 27, 2009 1:21 am Post subject: |
|
|
Acolyte
Joined: 08 Jan 2004
Posts: 60
|
just tested again with 6103 but the result is the same.
post the details into a PMR i guess. 2nd defect on the same bit of work!! 
ash. |
|
| Back to top |
|
|
| kimbert |
| Posted: Fri Feb 27, 2009 3:07 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| post the details into a PMR i guess |
Please reference this thread when you open the PMR. It will help to ensure that it gets routed quickly to somebody technical. |
|
| Back to top |
|
|
| alter11ego |
| Posted: Tue Oct 05, 2010 6:24 pm Post subject: |
|
|
Newbie
Joined: 02 Feb 2010
Posts: 5
|
Any updates to this thread please? I have a similar issue where I am trying to cast a string to a timestamp and want to preserve the timezone offset. But just like the OP the '+11:00' offset is lost in place of '+00:00'.
SET OutputRoot.MRM.DTField = CAST('2010-10-04T11:39:26.813+11:00' AS TIMESTAMP FORMAT 'IU');
DTField (which is a TIMESTAMP) becomes: '2010-10-04T00:39:26.813+00:00' - the +11:00 gets lost. How do I manipulate the timestamp to include the +11:00?
Were you able to resolve this issue?
Broker version: 6.0.2. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Oct 05, 2010 8:10 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| alter11ego wrote: |
Any updates to this thread please? I have a similar issue where I am trying to cast a string to a timestamp and want to preserve the timezone offset. But just like the OP the '+11:00' offset is lost in place of '+00:00'.
SET OutputRoot.MRM.DTField = CAST('2010-10-04T11:39:26.813+11:00' AS TIMESTAMP FORMAT 'IU');
DTField (which is a TIMESTAMP) becomes: '2010-10-04T00:39:26.813+00:00' - the +11:00 gets lost. How do I manipulate the timestamp to include the +11:00?
Were you able to resolve this issue?
Broker version: 6.0.2. |
Instead of cast as timestamp, have you tried cast as GMTTIMESTAMP? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| alter11ego |
| Posted: Tue Oct 05, 2010 8:48 pm Post subject: |
|
|
Newbie
Joined: 02 Feb 2010
Posts: 5
|
Thanks for the reply. I have tried GMTTIMESTAMP - this would have worked apart from the fact that the broker locale is incorrect due to another 6.0.2 defect summarised below.
"Broker takes the first 3 characters of TZ and uses it as the time zone, and it ignores the UTC offset. The issue is AIX sees EET as Australian Eastern Time (UTC+10), but broker sees EET as Eastern European time (UTC+2)."
Hence, GMTTIMESTAMP would have worked apart from the issue above. And note that I am prohibited from changing the broker timezone - I have to work on that confine.
Any other ideas? |
|
| Back to top |
|
|
|
|
