| Author |
Message
|
| wmbv7newbie |
 Posted: Tue Dec 02, 2014 10:28 pm Post subject: TimeStamp Conversion To GMTTIMEZONE Issue |
 |
|
Centurion
Joined: 13 May 2014
Posts: 121
|
Hi,
I'm having some issues converting a date/time from SFDC into a GMTTIMESTAMP, and thought I'd ask around as I don't particularly like my solution.
The problem I'm facing is down to the way that SFDC formats the timezone. Here's an example:
2014-11-14T13:17:25.000+0000
The timezone (0000) is missing a colon. It should be:
2014-11-14T13:17:25.000+00:00 or
2014-11-14T13:17:25.000Z
I get the following error when I attempt to cast this as a GMTTIMEZONE (using I or IU as the format)...
| Quote: |
| BIP2319E: Error casting ''Etc/GMT+'' to a timezone. |
In the end, I had to write a function to add the colon if it is missing. Does anyone know of a format specifier that will work with this SFDC date format? |
|
| Back to top |
|
 |
| Vitor |
| Posted: Wed Dec 03, 2014 5:52 am Post subject: Re: TimeStamp Conversion To GMTTIMEZONE Issue |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| wmbv7newbie wrote: |
| Does anyone know of a format specifier that will work with this SFDC date format? |
The I & IU are just helper formats. There's no good reason why you couldn't generate a format specifier that spells out this non-colon format.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| wmbv7newbie |
| Posted: Thu Dec 04, 2014 12:31 am Post subject: |
|
|
Centurion
Joined: 13 May 2014
Posts: 121
|
Tried below code -
| Code: |
DECLARE inTimeChar CHARACTER;
SET inTimeChar = InputRoot.XMLNSC.Orders.Order.Time;
DECLARE inTime GMTTIMESTAMP;
SET inTime = CAST(inTimeChar AS GMTTIMESTAMP FORMAT 'yyyy-MM-dd''T''HH:mm:ss.SSS''+0000');
SET OutputRoot.XMLNSC.gmttmstmpdate1 = inTime; |
The only problem here is the precision loss.
Input - <Time>2014-11-14T13:17:25.000+0000</Time>
Output - <gmttmstmpdate1>2014-11-14T13:17:25</gmttmstmpdate1>
Can someone suggest any improvements to get the usual GMTTIMESTAMP format of I or IU? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Dec 04, 2014 1:06 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
you are nearly there.
This bit in the InfoCentre is the key thing for you
| Code: |
ZZZZZ time zone (as ZZZ, but no colon) (+/-nnnn) Text +0300
|
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| wmbv7newbie |
| Posted: Thu Dec 04, 2014 1:52 am Post subject: |
|
|
Centurion
Joined: 13 May 2014
Posts: 121
|
Thanks @smdavies99!
I did try -
| Code: |
| SET inTime = CAST(inTimeChar AS GMTTIMESTAMP FORMAT 'yyyy-MM-dd''T''HH:mm:ss.SSS''+''ZZZZZ'); |
Same result though. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Dec 04, 2014 3:23 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
you don't need the + in the format string.
Look closely at the meaning of 'ZZZZZ' in the InfoCentre. It includes the '+'
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| wmbv7newbie |
| Posted: Thu Dec 04, 2014 3:34 am Post subject: |
|
|
Centurion
Joined: 13 May 2014
Posts: 121
|
I just noticed while testing through debugger that the value coming out of Compute is -
| Code: |
XMLNSC
gmttmstmpdate1 2014-11-14 13:17:25.000
|
While, when I write it onto the queue, it comes as -
| Code: |
| <gmttmstmpdate1>2014-11-14T13:17:25</gmttmstmpdate1> |
|
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Dec 04, 2014 5:15 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| wmbv7newbie wrote: |
I just noticed while testing through debugger that the value coming out of Compute is -
| Code: |
XMLNSC
gmttmstmpdate1 2014-11-14 13:17:25.000
|
While, when I write it onto the queue, it comes as -
| Code: |
| <gmttmstmpdate1>2014-11-14T13:17:25</gmttmstmpdate1> |
|
Take a user trace with some Trace nodes. The debugger lies, or at least interprets what it sees. It's not reliable (IMHO) for this kind of highly detailed, highly specific, data format / parsing problem.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Dec 04, 2014 5:56 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
This ESQL
| Code: |
DECLARE inTimeChar CHARACTER '2014-11-14T13:17:25.000+0000';
DECLARE inTime GMTTIMESTAMP;
SET inTime = CAST(inTimeChar AS GMTTIMESTAMP FORMAT 'yyyy-MM-dd''T''HH:mm:ss.SSSZZZZZ');
set OutputRoot.XMLNSC.Data.A = inTimeChar;
set OutputRoot.XMLNSC.Data.B = cast(inTime as char format 'IU');
|
produced this data on a Queue
| Code: |
<Data>
<A>2014-11-14T13:17:25.000+0000</A>
<B>2014-11-14T13:17:25.000Z</B>
</Data>
|
I have test flow that I use for testing all sorts of date/time conversions. This case it just one more example.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| wmbv7newbie |
| Posted: Thu Dec 04, 2014 6:27 am Post subject: |
|
|
Centurion
Joined: 13 May 2014
Posts: 121
|
First, thanks @smdavies99 for the troubleshoot.
I am surprised and confused. After you guided me on the correct usage of ZZZZZ, i used the same code as yours above but had same results as before with the loss of precision.
I was viewing the queue through RFHUtil. I hope that wasn't the culprit to show me modified output.
I have no other option than to try it again.
Thanks! |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Dec 04, 2014 6:43 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
The output data I put in my previous post was a cut/paste from the Data window of Rfhutil. I was browsing the queue where the test message was put.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Thu Dec 04, 2014 10:01 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| wmbv7newbie wrote: |
Tried below code -
| Code: |
DECLARE inTimeChar CHARACTER;
SET inTimeChar = InputRoot.XMLNSC.Orders.Order.Time;
DECLARE inTime GMTTIMESTAMP;
SET inTime = CAST(inTimeChar AS GMTTIMESTAMP FORMAT 'yyyy-MM-dd''T''HH:mm:ss.SSS''+0000');
SET OutputRoot.XMLNSC.gmttmstmpdate1 = inTime; |
|
The FORMAT clause here applies to the input string (inTimeChar), not to the timestamp (inTime). Timestamps do not have a format.
| wmbv7newbie wrote: |
| The only problem here is the precision loss. |
In this case, the ESQL assigns a GMTTIMESTAMP value to the output field, and the XMLNSC parser later converts the timestamp value to an output string when it writes the message, using a default format. That's why it looks different in the debugger. The default output format may also strip some trailing zeros from the fractional seconds in the timestamp value.
Note how smdavies99 used a second CAST to format the output string. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Dec 04, 2014 10:44 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Note, also, that an XML date field has a very specific format, if you want to interpret it as an xsd:date/xsd:datetime/etc.
If you want to use *any* other format for a date or timestamp in an XML document, you have to treat it as a character or numeric value. |
|
| Back to top |
|
|
| wmbv7newbie |
| Posted: Thu Dec 04, 2014 10:59 pm Post subject: |
|
|
Centurion
Joined: 13 May 2014
Posts: 121
|
Yep. My fault to leave the timestamp as it is and hence, the broker converted it to string as it thought appropriate.
I am now casting it to String and getting the expected results.
Thanks everyone for your guidance! |
|
| Back to top |
|
|
|
|
