| Author |
Message
|
| abcnil |
 Posted: Wed Jun 04, 2014 3:05 am Post subject: TimeStamp in MB-8 |
 |
|
Apprentice
Joined: 29 Mar 2012
Posts: 36
|
I am using MB8 with Fix pack 3
I see some issue with Timestamp casting.
Here what i tried :
DECLARE dT TIMESTAMP;
set dT = CAST(OutputRoot.XMLNSC.Test.In as TIMESTAMP FORMAT 'IU');
set OutputRoot.XMLNSC.Test.CurrentTimestamp = CAST(CURRENT_TIMESTAMP as CHAR FORMAT 'IU');
set OutputRoot.XMLNSC.Test.Out1 = CAST(dT as CHAR FORMAT 'IU');
set OutputRoot.XMLNSC.Test.Out2 = CAST(dT as CHAR FORMAT 'MM/dd/yyyy hh:mm a zzzz');
InMessage:
<Test>
<In>2014-04-12T13:30:00-05:00</In>
</Test>
Ouput is coming as:
<Test>
<CurrentTimestamp>2014-06-04T13:57:45.054+03:00</CurrentTimestamp>
</Test>
It should come as:
Test>
<In>2014-04-12T13:30:00-05:00</In>
<CurrentTimestamp>2014-06-04T13:57:45.054+03:00</CurrentTimestamp>
<Out1>2014-04-12T12:30:00.000-05:00</Out1>
<Out2>04/12/2014 12:30 PM Central Daylight Time</Out2>
</Test>
I dont understand whats wrong with it.
Experts could you please help me understand.  |
|
| Back to top |
|
 |
| abcnil |
| Posted: Wed Jun 04, 2014 3:06 am Post subject: |
|
|
Apprentice
Joined: 29 Mar 2012
Posts: 36
|
Typo:
Code is :
set OutputRoot = InputRoot;
set dT = CAST(OutputRoot.XMLNSC.Test.In as TIMESTAMP FORMAT 'IU');
set OutputRoot.XMLNSC.Test.CurrentTimestamp = CAST(CURRENT_TIMESTAMP as CHAR FORMAT 'IU');
set OutputRoot.XMLNSC.Test.Out1 = CAST(dT as CHAR FORMAT 'IU');
set OutputRoot.XMLNSC.Test.Out2 = CAST(dT as CHAR FORMAT 'MM/dd/yyyy hh:mm a zzzz');
|
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Jun 04, 2014 3:36 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
What happens if you use GMTTIMESTAMP?
| Code: |
| set dT = CAST(OutputRoot.XMLNSC.Test.In as GMTTIMESTAMP FORMAT 'IU'); |
btw, I assume that your expected output is
| Code: |
| <Out1>2014-04-12T13:30:00.000-05:00</Out1> |
and not
| Code: |
| <Out1>2014-04-12T12:30:00.000-05:00</Out1> |
_________________
Before you criticize someone, walk a mile in their shoes. That way you're a mile away, and you have their shoes too. |
|
| Back to top |
|
|
| abcnil |
| Posted: Wed Jun 04, 2014 3:45 am Post subject: |
|
|
Apprentice
Joined: 29 Mar 2012
Posts: 36
|
Thanks kimbert for Quick Reply...
i tried with GMTTIMESTAMP too. But no change in output.
Tag Out1 and Out2 are not getting set. |
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Jun 04, 2014 3:47 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
Not getting set? I assume you mean that they are getting set to incorrect values?!
_________________
Before you criticize someone, walk a mile in their shoes. That way you're a mile away, and you have their shoes too. |
|
| Back to top |
|
|
| abcnil |
| Posted: Wed Jun 04, 2014 3:51 am Post subject: |
|
|
Apprentice
Joined: 29 Mar 2012
Posts: 36
|
As mentioned :
Output is coming as:
<Test>
<CurrentTimestamp>2014-06-04T13:57:45.054+03:00</CurrentTimestamp>
</Test>
Out1 and Out2 tags are not getting set. |
|
| Back to top |
|
|
| abcnil |
| Posted: Wed Jun 04, 2014 5:39 am Post subject: |
|
|
Apprentice
Joined: 29 Mar 2012
Posts: 36
|
Like to give more details behind my this sample test code:
My input xml has tag with datatype as xsd:datetime and its value is coming
in format [2014-05-12T08.40.01.987654+01:00] CET Time (UTC+1)
and
I need to convert it to 2014-05-12T09.40.00.123456+02:00 Local Timezone (UTC+2)
n then i would like to convert it to any other format say ['yyyy-MM-dd-HH.mm.ss.SSSSSS']
But when i do it give me following exception "Text:CHARACTER:Unable to identify date or time I/T pattern"
my code:
| Code: |
DECLARE dt TIMESTAMP;
SET dt = CAST(inputMsgRef.q1:ExecTimestamp AS TIMESTAMP FORMAT 'IU');
SET outputMsgRef.ns:executionTs = CAST(dt AS CHAR FORMAT 'yyyy-MM-dd-HH.mm.ss.SSSSSS');
|
Exception Tree:
Insert
Type:INTEGER:5
Text:CHARACTER:SET dt = CAST(inputMsgRef.q1:ExecTimestamp AS TIMESTAMP FORMAT 'IU');
RecoverableException
File:CHARACTER:F:\build\slot1\S800_P\src\DataFlowEngine\ImbRdl\ImbRdlTypeCast.cpp
Line:INTEGER:268
Function:CHARACTER:SqlTypeCast::evaluate
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2521
Text:CHARACTER:Error while casting
Insert
Type:INTEGER:5
Text:CHARACTER:.Response_SubFlow_Compute.Main
Insert
Type:INTEGER:5
Text:CHARACTER:51.18
Insert
Type:INTEGER:5
Text:CHARACTER:'2014-03-15T16:13:27.1298+01:00'
Insert
Type:INTEGER:5
Text:CHARACTER:TIMESTAMP
RecoverableException
File:CHARACTER:F:\build\slot1\S800_P\src\CommonServices\ImbTimeStampFormatter.cpp
Line:INTEGER:2598
Function:CHARACTER:ImbTimeStampFormatter::identifyIFormatPattern
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:3204
Text:CHARACTER:Unable to identify date or time I/T pattern
Insert
Type:INTEGER:5
Text:CHARACTER:2014-03-15T16:13:27.1298+01:00
Insert
Type:INTEGER:5
Text:CHARACTER:IU
Insert
Type:INTEGER:5
Text:CHARACTER:2014-03-15T16:13:27.1298+01:00
Insert
Type:INTEGER:5
Text:CHARACTER:I
Dont know why 
Hoping for |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jun 04, 2014 5:41 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| If you are are parsing the input document using a message model (an XML schema), then it is likely that your field is already an ESQL timestamp, not a character. |
|
| Back to top |
|
|
| abcnil |
| Posted: Wed Jun 04, 2014 5:55 am Post subject: |
|
|
Apprentice
Joined: 29 Mar 2012
Posts: 36
|
| mqjeff wrote: |
| If you are are parsing the input document using a message model (an XML schema), then it is likely that your field is already an ESQL timestamp, not a character. |
Exactly!!!
once this come i need to change to local timezone so i am using this statement
| Code: |
| SET dt = CAST(inputMsgRef.q1:ExecTimestamp AS TIMESTAMP FORMAT 'IU'); |
So cant we FORMAT one Timestamp to other ? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jun 04, 2014 6:10 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| abcnil wrote: |
So cant we FORMAT one Timestamp to other ? |
NO.
It has no format. Format is used to parse and serialize. |
|
| Back to top |
|
|
| abcnil |
| Posted: Wed Jun 04, 2014 6:35 am Post subject: |
|
|
Apprentice
Joined: 29 Mar 2012
Posts: 36
|
Thanks mqjeff for it...
then question comes how can i change the timezone from UTC+1 to my local UTC+3 and do format it to another pattern  |
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Jun 04, 2014 6:59 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
If you are communicating dates and times then it would be wise to use UTC everywhere. Let the display application do the conversion to local time ( time zones are mainly useful for humans, not computers ).
What value do you get if you CAST the input value to GMTTIMESTAMP?
_________________
Before you criticize someone, walk a mile in their shoes. That way you're a mile away, and you have their shoes too. |
|
| Back to top |
|
|
| abcnil |
| Posted: Thu Jun 05, 2014 4:41 am Post subject: |
|
|
Apprentice
Joined: 29 Mar 2012
Posts: 36
|
i tried with GMTTIMESTAMP.
With lot of brainstorming i came to this logic.
Now my code look like this.
| Code: |
DECLARE inTime GMTTIMESTAMP;
DECLARE outTime TIMESTAMP;
DECLARE inTimeChar CHARACTER;
DECLARE outTimeChar CHARACTER;
SET inTimeChar = inputMsgRef.q1:ExecTimestamp;
SET inTime = CAST(inTimeChar AS GMTTIMESTAMP FORMAT 'IU');
SET outTime = CAST(inTime AS TIMESTAMP);
SET outTimeChar = CAST(outTime AS CHARACTER FORMAT 'yyyy-MM-dd''T''HH:mm:ss ZZZ'); |
inTimeChar = 2014-03-15T16:13:27.1298+01:00
In Debug i can see error:
on line
| Code: |
SET inTime = CAST(inTimeChar AS GMTTIMESTAMP FORMAT 'IU');
|
Text:CHARACTER:Unable to identify date or time I/T pattern
I doubt something wrong with MB8(Pack3).
Its not able to convert CHAR to TIMESTAMP/GMTTIMESTAMP for FORMAT IU.
I remember running similar line of code on 6.1, which worked fine.
 |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jun 05, 2014 5:02 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| abcnil wrote: |
| I remember running similar line of code on 6.1, which worked fine. |
Yes, I remember a lot of things in WMBv6.1 which didn't error out when by rights they should have. Things are tighter now.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| abcnil |
| Posted: Thu Jun 05, 2014 5:30 am Post subject: |
|
|
Apprentice
Joined: 29 Mar 2012
Posts: 36
|
Also,
As per Documentation:
http://www-01.ibm.com/support/knowledgecenter/?lang=en#!/SSKM8N_8.0.0/com.ibm.etools.mft.doc/ak01005_.htm
| Quote: |
nput dateTime element containing 2009-02-20T06:08:07-08:00 could be copied from the input message tree to the output message tree and appear in an output message in exactly the same format. However, if the element is cast as character, using format IU, by a broker running GMT the result would be 2009-02-20T06:08:07.000Z.
|
My Broker is running in UTC+3, so I tried this :
q1:ExecTimestamp is xsd:datetime type
and input value is
<q1:ExecTimestamp>2014-03-15T16:13:27.1298+01:00</q1:ExecTimestamp>
My Code
| Code: |
SET Environment.ExecutionTs = CAST(inputCardOrderRsHRsMsgRef.q1:ExecTimestamp AS CHARACTER FORMAT 'IU');
|
Output is coming as : Environment var from Debug:
ExecutionTs:CHARACTER:2014-03-15T16:13:27.1298+01:00
WHY ? Am i doing something wrong?? |
|
| Back to top |
|
|
|
|
