| Author |
Message
|
| howardl |
 Posted: Mon Jun 27, 2011 10:37 am Post subject: GMTTIMESTAMP from SOAP message |
 |
|
Novice
Joined: 13 Jul 2010
Posts: 11
|
I have a SOAP request coming in that includes a timestamp type xs:dateTime and when I set it as GMTTIMESTAMP in ESQL the timezome is being ignored.
For example:
<m:TimeStamp>2011-06-11T09:30:47-02:00</m:TimeStamp>
DECLARE time GMTTIMESTAMP;
SET time = InputRoot.XMLNSC.msg:Message.msg:TimeStamp;
time-> 2011-06-11 13:30:47.000
<m:TimeStamp>2011-06-11T09:30:47-04:00</m:TimeStamp>
DECLARE time GMTTIMESTAMP;
SET time = InputRoot.XMLNSC.msg:Message.msg:TimeStamp;
time-> 2011-06-11 13:30:47.000
<m:TimeStamp>2011-06-11T09:30:47.1Z</m:TimeStamp>
DECLARE time GMTTIMESTAMP;
SET time = InputRoot.XMLNSC.msg:Message.msg:TimeStamp;
time-> 2011-06-11 13:30:47.000
etc...
Am I missing a step somewhere for this to work as I'd like? |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Mon Jun 27, 2011 10:41 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| So what is the difference between a GMTTIMESTAMP and a TIMESTAMP? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Jun 27, 2011 1:56 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
So your time stamp says 09:30:47-02:00
That should give you GMT 11:30:47 instead you are getting 13:30:47 which is 09:30:47-04:00...
So you need to do a CAST and specify the format instead of what you are using to assign the source to your gmttimestamp field...
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| howardl |
| Posted: Tue Jun 28, 2011 6:28 am Post subject: |
|
|
Novice
Joined: 13 Jul 2010
Posts: 11
|
I am using formatting successfully to output times in the format I want when I already have the timestamp, but I still can't seem to get it to read in a timestamp correctly.
<m:TimeStamp>2011-06-11T09:30:47-01:00</m:TimeStamp>
DECLARE time GMTTIMESTAMP;
SET time = InputRoot.XMLNSC.msg:Message.msg:TimeStamp;
SET time = CAST(InputRoot.XMLNSC.msg:Message.msg:TimeStamp AS GMTTIMESTAMP FORMAT 'IU');
time-> 2011-06-11 13:30:47.000
<m:TimeStamp>2011-06-11T09:30:47-01:00</m:TimeStamp>
DECLARE time GMTTIMESTAMP;
SET time = InputRoot.XMLNSC.msg:Message.msg:TimeStamp;
SET time = CAST(InputRoot.XMLNSC.msg:Message.msg:TimeStamp AS GMTTIMESTAMP FORMAT 'I');
time-> 2011-06-11 13:30:47.000
<m:TimeStamp>2011-06-11T09:30:47-01:00</m:TimeStamp>
DECLARE time GMTTIMESTAMP;
SET time = InputRoot.XMLNSC.msg:Message.msg:TimeStamp;
SET time = CAST(InputRoot.XMLNSC.msg:Message.msg:TimeStamp AS GMTTIMESTAMP FORMAT 'T');
time-> 2011-06-11 13:30:47.000
<m:TimeStamp>2011-06-11T09:30:47-01:00</m:TimeStamp>
DECLARE time GMTTIMESTAMP;
SET time = InputRoot.XMLNSC.msg:Message.msg:TimeStamp;
SET time = CAST(InputRoot.XMLNSC.msg:Message.msg:TimeStamp AS GMTTIMESTAMP FORMAT 'yyyy-MM-dd''T''HH:mm:ssZZZ');
time-> 2011-06-11 13:30:47.000
Even when I just try to turn the source into a CHAR it is dropping the source's timezome.
<m:TimeStamp>2011-06-11T09:30:47-01:00</m:TimeStamp>
DECLARE tim CHAR;
SET tim = CAST(InputRoot.XMLNSC.msg:Message.msg:TimeStamp AS CHARACTER FORMAT 'IU');
tim -> 2011-06-11T09:30:47.000-04:00 |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Jun 28, 2011 8:02 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
The trick to manipulating any time value is to always use GMT time, nevermind the timezone.
The only time you would ever worry about what timezone it is, is when it is displayed to a human.
All other times, in log files, database fields, calculations, etc., you would always use GMT time without any TZ offset.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| howardl |
| Posted: Tue Jun 28, 2011 9:48 am Post subject: |
|
|
Novice
Joined: 13 Jul 2010
Posts: 11
|
| Always using GMT time is a constraint we have used for all internal services as you say lancelotinc. This however is a soap call from a vendor product where I wanted the flexibility for if the time was ever not GMT. I am starting to wonder though if timezones are even supported using the XMLNSC domain. A trace shows the same value TIMESTAMP '2011-06-11 09:30:47' (TIMESTAMP) no matter what the timezone is set to on the input. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Jun 28, 2011 10:15 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| howardl wrote: |
| Always using GMT time is a constraint we have used for all internal services as you say lancelotinc. This however is a soap call from a vendor product where I wanted the flexibility for if the time was ever not GMT. I am starting to wonder though if timezones are even supported using the XMLNSC domain. A trace shows the same value TIMESTAMP '2011-06-11 09:30:47' (TIMESTAMP) no matter what the timezone is set to on the input. |
How do you know the callee's timezone? Computer-to-computer communications are always GMT based. When DST rules change, if they were not GMT based, you would have to constantly update the rules. Computer-to-computer communications should never have timezone functionality built in.
Timezones are for humans, not computers. Computers do not sleep or live in timezones or care about daylight.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| Luke |
| Posted: Wed Jun 29, 2011 3:57 am Post subject: |
|
|
Centurion
Joined: 10 Nov 2008
Posts: 128
Location: UK
|
Hi
I'm guessing that when you CAST to a GMTTIMESTAMP, the Broker is treating your input as a local date and time, and subtracting the LOCAL_TIMEZONE to calculate the GMT date and time. That's the documented behaviour for a CAST between TIMESTAMP and GMTTIMESTAMP, although not entirely clear to me if the same should be happening when casting from character with a pattern that specifies timezone ...
What results do you get when you cast the input to a TIMESTAMP rather than a GMTTIMESTAMP?
| lancelotlinc wrote: |
How do you know the callee's timezone? Computer-to-computer communications are always GMT based. When DST rules change, if they were not GMT based, you would have to constantly update the rules. Computer-to-computer communications should never have timezone functionality built in.
Timezones are for humans, not computers. Computers do not sleep or live in timezones or care about daylight. |
But the date and time in the incoming message includes the timezone. That's how you know the callee's timezone? Isn't it as valid to say the time here is 9:30 and I'm at GMT -1, as it is to say the time is 10:30 GMT/UTC? |
|
| Back to top |
|
|
| Luke |
| Posted: Wed Jun 29, 2011 4:08 am Post subject: |
|
|
Centurion
Joined: 10 Nov 2008
Posts: 128
Location: UK
|
|
| Back to top |
|
|
| howardl |
| Posted: Wed Jun 29, 2011 1:08 pm Post subject: |
|
|
Novice
Joined: 13 Jul 2010
Posts: 11
|
| Thanks Luke! That thread was exactly what I have been looking for. The string workaround works perfectly. The defect still exists in WMB 7.0.0.2. Does anyone know if this defect has been submitted or if it is scheduled to be corrected in a fix pack? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Jun 29, 2011 8:11 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
I was looking at the above reference thread (bottom) and then googled some and found this:
http://www.timeanddate.com/library/abbreviations/timezones/au/est.html
What I found really distressing is that you really want a homogeneous time zone denomination. You should not be able to have EST being both UTC-5 and UTC+10...
So my reference would be: when in doubt list the abbreviations from your JVM (easy java pgm) and go by these...
And as reference most *nix os platforms allow you to define your own complete with abbreviation, offset, and start and stop of DST, should the one you want to use not be in the defaults (see export TZ)...
The most compelling case is made with XML where you do not apply a timezone but a timezone offset... (so it is up to you to translate the timezone into a timezone offset...)
http://www.w3.org/TR/timezone/
Have fun
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
