MQSeries.net :: View topic - Unable to generate timestamp with timezone in Mapping node

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Unable to generate timestamp with timezone in Mapping node

Goto page 1, 2, 3 Next

Unable to generate timestamp with timezone in Mapping node

« View previous topic :: View next topic »

Author

Message

Cogito-Ergo-Sum

Posted: Mon Nov 19, 2012 10:04 am Post subject: Unable to generate timestamp with timezone in Mapping node

Master

Joined: 07 Feb 2006
Posts: 293
Location: Bengaluru, India

I have a CSV message that contains two fields viz. inputDate and inputTime whose values are for example, 2012-11-19 and 23:22:00+05:30 respectively. In the Mapping node, I am wiring them to fn:adjust-dateTime-to-timezone function such that it 'matches' the expression fn:adjust-dateTime-to-timezone(fn:dateTime($inputDate,$inputTime),fn:timezone-from-time($inputTime)).
The resulting value is a timestamp without the timezone. How do I obtain the timezone value ?

Also, the DFDL parser throws an error if the value of inputTime did not have the timezone mentioned explicitly. That is, a value of 00:00:32 is an error.

Code:

CTDP3029E: Invalid calendar value '00:00:32' for element 'inputTime'. Parsing failed at offset 8.
ParsedDataRegion[SimpleContent, startOffset = 28, length = 8, scd = #xscd(/schemaElement::SampleCsv/type::0/model::sequence/schemaElement::sensorMqttCsv/type::0/model::sequence/schemaElement::inputTime)]

Any idea, why this happens ?

WMB 8.0.0.0 on Linux
_________________
ALL opinions are welcome.
-----------------------------
Debugging tip:When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
---Sherlock Holmes

lancelotlinc

Posted: Mon Nov 19, 2012 10:11 am Post subject:

Jedi Knight

Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA

You may have better luck in a Compute node ( 'LOCAL_TIMEZONE' ). Also, Java functions can help when called from an ESQL compute node.

I like the concept of the new mapping functionality. I hope some additional polish can be applied.

Code:

SET crtTz = SUBSTRING( CAST( CAST( LOCAL_TIMEZONE AS INTERVAL HOUR ) AS CHARACTER ) FROM 11 FOR 2 );

_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER

lancelotlinc

Posted: Mon Nov 19, 2012 10:28 am Post subject:

Jedi Knight

Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA

P. S. It's so sad that people who write business requirements insist on dealing with timezone information when the transaction is machine readable. ESBs have no need to utilize timezone information. Timezone information is only for human consumption. All machine processes should only reference GMT. Machines don't need to sleep and therefore don't care what timezone it is.

_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER

kimbert

Posted: Mon Nov 19, 2012 4:16 pm Post subject:

Jedi Council

Joined: 29 Jul 2003
Posts: 5542
Location: Southampton

Quote:

Also, the DFDL parser throws an error if the value of inputTime did not have the timezone mentioned explicitly. That is, a value of 00:00:32 is an error.

CTDP3029E: Invalid calendar value '00:00:32' for element 'inputTime'. Parsing failed at offset 8.
ParsedDataRegion[SimpleContent, startOffset = 28, length = 8, scd = #xscd(/schemaElement::SampleCsv/type::0/model::sequence/schemaElement::sensorMqttCsv/type::0/model::sequence/schemaElement::inputTime)]

Any idea, why this happens ?

Check the definition of the dateTime element in the DFDL schema. I assume that its calendarPattern property is requesting a time zone. If you delete the final section of the calendarPattern string then it should stop complaining.

If the calendarPattern is being inherited from the IBM-supplied xsd then make sure that you do not edit the IBM-supplied xsd. Set the value in your own xsd by editing the calendarPattern property for the element.
Alternatively, if you have a lot of date/time fields and you want to use your own default value for all of them then create a default format block for your own xsd and reference the IBM-supplied format from your new default format.

Cogito-Ergo-Sum

Posted: Mon Nov 19, 2012 6:19 pm Post subject:

Master

Joined: 07 Feb 2006
Posts: 293
Location: Bengaluru, India

lancelotlinc wrote:

You may have better luck in a Compute node ( 'LOCAL_TIMEZONE' ).

I guess, I will have to go down that path because even the Custom ESQL invocation from Mapping node did not work.

So far, the Mapping node has failed to impress me ....

kimbert wrote:

So much for the wizard !!

_________________
ALL opinions are welcome.
-----------------------------
Debugging tip:When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
---Sherlock Holmes

smdavies99

Posted: Mon Nov 19, 2012 10:50 pm Post subject:

Jedi Council

Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.

lancelotlinc wrote:

Whilst I agree in principle with you and you only have to see the mess that many of the systems in Jordan are in because the Gov decided at the very last moment NOT to put the clocks back by one hour at the end of October keeping everything at GMT is not the answer to many system problems.

The majority of the servers that I'm using/connecting to are running on LOCAL time but we pass messages between them with times as GMT. We have 150+ displays around the place all showing the time (local) and flight arrival and departure times in LOCAL time. I really hope you are not suggesting that we switch these to show GMT?
When you are dealing with systems around the world using a common time base is a good idea and there are many international systems that do just that. The sad thing is that some users of these systems don't 'get it' and insist on sending messages with local timestamps rather than GMT(the aggreed time base).

At least we don't have to cater for the silliness that is the USA with AM & PM except in information we receive that originates in the US. Even Canada has seen the light and gone to the 24hr clock.

As an aside, I write fiction in my spare time and my US editor keeps changing all the times from 24hr clock to AP/PM even for times that are nothing to do with the US. She says that my US readers don't understand the 24hr clock. So in my latest story, I've had the hero ask their US Sidekick this question 'What is for <redacted> hard about the 24hr clock that you don't undersdand it?' when the sidekick turns up 12 hours late for an appointment. Please don't ask for the titles of my books or my pen name because I won't give it out. This is not the place for that sort of dicussion anyway.
_________________
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.

Cogito-Ergo-Sum

Posted: Mon Nov 19, 2012 11:21 pm Post subject:

Master

Joined: 07 Feb 2006
Posts: 293
Location: Bengaluru, India

smdavies wrote:

As an aside, I write fiction in my spare time

You inspire me !

smdaveies wrote:

'What is for <redacted> hard about the 24hr clock that you don't undersdand it?' when the sidekick turns up 12 hours late for an appointment.

kimbert

Posted: Tue Nov 20, 2012 1:39 am Post subject:

Jedi Council

Joined: 29 Jul 2003
Posts: 5542
Location: Southampton

Quote:

So much for the wizard !!

...which is probably what the other set of users would have said if the default calendar pattern had *not* included the time zone.
You can please all of the people some of the time, and some of the people all of the time, but...

Cogito-Ergo-Sum

Posted: Tue Nov 20, 2012 1:43 am Post subject:

Master

Joined: 07 Feb 2006
Posts: 293
Location: Bengaluru, India

So I set out to get the time-stamp with time zone in ESQL. The end result is, the final value still serializes without the time zone information.

I prepared a DFDL message model of the incoming CSV that has two fields inputDate and inputTime amongst others. To this model, I added another field as outTimestamp, date type dateTime with pattern as YYYY-MM-dd'T'HH:mm:ssZZZ and with a minOccurs of 0 (so that the user does not have to enter this). I chose this data type and the pattern because this is the pattern that needs to be mapped to the target element in the XML document.

After the incoming CSV is parsed, I have a Compute node that does the following.

1. Get the inputDate and inputTime.
2. Get time zone hour-to-minute value from the local time zone.
3. Create a time-stamp value using string values of inputDate, inputTime and time zone offset with the format pattern as 'YYYY-MM-dd''T''HH:mm:ssZZZ'.
4. Assign this time-stamp value to outTimestamp of the DFDL model

With the output of the Compute node, a mapping is done between this DFDL model and an XML schema in a Mapping node.

The trailing ZZZ is for the time zone value as +05:30, for example. Since the time zone offset is two digits and the hour value from the LOCAL_TIMEZONE interval is 1 digit, I had to do some string manipulation trickery. Eventually, all the string inputs to step 3 are generated correctly, but, the target variable does not have the time zone.

Code:

BROKER SCHEMA sample

CREATE COMPUTE MODULE SampleFlow_CMP01
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
   CALL CopyMessageHeaders();
   CALL CopyEntireMessage();

   Declare inputDate Date InputRoot.DFDL.SampleCsv.sampleCsv.inputDate;
   Declare inputTime Time InputRoot.DFDL.SampleCsv.sampleCsv.inputTime;
   Declare timezoneHourMinute Interval Cast(Local_Timezone as Interval Hour to Minute);

   Declare inputDateChar Character Cast(inputDate as Character format 'YYYY-MM-dd');
   Declare inputTimeChar Character Cast(inputTime as Character format 'HH:mm:ss');
   Declare timezoneHourMinuteString Character Cast(timezoneHourMinute as Character) ;
   Declare timezoneChar Character ;
   Set timezoneChar = Substring(timezoneHourMinuteString from 10 for 8);
   Set timezoneChar = Trim(Replace(timezoneChar, '''', ' '));

   If Position(':' in timezoneChar) = 3 then
      Set timezoneChar = Substring(timezoneChar from 1 for 1)||
                     '0'||
                     Substring(timezoneChar from 2);
   End If;

   Declare outTimestamp Timestamp Cast(inputDateChar||'T'||inputTimeChar||timezoneChar
      as Timestamp Format 'YYYY-MM-dd''T''HH:mm:ssZZZ');

   Set OutputRoot.DFDL.SampleCsv.sampleCsv.outTimestamp = outTimestamp ;

   RETURN TRUE;
END;

CREATE PROCEDURE CopyMessageHeaders() BEGIN
   DECLARE I INTEGER 1;
   DECLARE J INTEGER;
   SET J = CARDINALITY(InputRoot.*[]);
   WHILE I < J DO
      SET OutputRoot.*[I] = InputRoot.*[I];
      SET I = I + 1;
   END WHILE;
END;

CREATE PROCEDURE CopyEntireMessage() BEGIN
   SET OutputRoot = InputRoot;
END;
END MODULE;

Here is a portion of the user trace.

Code:

2012-11-20 03:51:39.926276 60 UserTrace BIP2537I: Node 'sample.SampleFlow.CMP01': Executing statement ''DECLARE outTimestamp TIMESTAMP CAST(inputDateChar || 'T' || inputTimeChar || timezoneChar AS TIMESTAMP FORMAT 'YYYY-MM-dd'T'HH:mm:ssZZZ');'' at ('sample.SampleFlow_CMP01.Main', '23.3').
2012-11-20 03:51:39.926284 60 UserTrace BIP2539I: Node 'sample.SampleFlow.CMP01': Evaluating expression ''inputDateChar'' at ('sample.SampleFlow_CMP01.Main', '23.43'). This resolved to ''inputDateChar''. The result was '''2012-11-19'''.
2012-11-20 03:51:39.926296 60 UserTrace BIP2539I: Node 'sample.SampleFlow.CMP01': Evaluating expression ''inputDateChar || 'T''' at ('sample.SampleFlow_CMP01.Main', '23.57'). This resolved to '''2012-11-19' || 'T'''. The result was '''2012-11-19T'''.
2012-11-20 03:51:39.926304 60 UserTrace BIP2539I: Node 'sample.SampleFlow.CMP01': Evaluating expression ''inputTimeChar'' at ('sample.SampleFlow_CMP01.Main', '23.64'). This resolved to ''inputTimeChar''. The result was '''23:30:00'''.
2012-11-20 03:51:39.926312 60 UserTrace BIP2539I: Node 'sample.SampleFlow.CMP01': Evaluating expression ''inputDateChar || 'T' || inputTimeChar'' at ('sample.SampleFlow_CMP01.Main', '23.62'). This resolved to '''2012-11-19T' || '23:30:00'''. The result was '''2012-11-19T23:30:00'''.
2012-11-20 03:51:39.926328 60 UserTrace BIP2539I: Node 'sample.SampleFlow.CMP01': Evaluating expression ''timezoneChar'' at ('sample.SampleFlow_CMP01.Main', '23.80'). This resolved to ''timezoneChar''. The result was '''-05:00'''.
2012-11-20 03:51:39.926336 60 UserTrace BIP2539I: Node 'sample.SampleFlow.CMP01': Evaluating expression ''inputDateChar || 'T' || inputTimeChar || timezoneChar'' at ('sample.SampleFlow_CMP01.Main', '23.78'). This resolved to '''2012-11-19T23:30:00' || '-05:00'''. The result was '''2012-11-19T23:30:00-05:00'''.
2012-11-20 03:51:39.926512 60 UserTrace BIP2539I: Node 'sample.SampleFlow.CMP01': Evaluating expression ''CAST(inputDateChar || 'T' || inputTimeChar || timezoneChar AS TIMESTAMP FORMAT 'YYYY-MM-dd'T'HH:mm:ssZZZ')'' at ('sample.SampleFlow_CMP01.Main', '23.38'). This resolved to ''CAST('2012-11-19T23:30:00-05:00' AS TIMESTAMP FORMAT 'YYYY-MM-dd'T'HH:mm:ssZZZ' )''. The result was ''TIMESTAMP '2012-11-19 23:30:00'''.
2012-11-20 03:51:39.926528 60 UserTrace BIP2537I: Node 'sample.SampleFlow.CMP01': Executing statement ''SET OutputRoot.DFDL.SampleCsv.sampleCsv.outTimestamp = outTimestamp;'' at ('sample.SampleFlow_CMP01.Main', '26.3').
2012-11-20 03:51:39.926544 60 UserTrace BIP2539I: Node 'sample.SampleFlow.CMP01': Evaluating expression ''outTimestamp'' at ('sample.SampleFlow_CMP01.Main', '26.70'). This resolved to ''outTimestamp''. The result was ''TIMESTAMP '2012-11-19 23:30:00'''.

What am I missing ?

One other option I am mulling is to leave 2012-11-19T23:30:00-05:00 as string and use the fn:dateTime XPath transform in the subsequent Mapping node. I am not too confident about it ....
_________________
ALL opinions are welcome.
-----------------------------
Debugging tip:When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
---Sherlock Holmes

fjb_saper

Posted: Tue Nov 20, 2012 3:47 am Post subject:

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY

Didn't see whether the ESQL on Date Time has changed to take into consideration the offset at the time of the input vs at the time of execution...

This is another reason why I prefer my manipulations for TZ to happen in Java. However please remember that if not used as ThreadLocal, date formatters are not thread safe...

Have fun

_________________
MQ & Broker admin

lancelotlinc

Posted: Tue Nov 20, 2012 6:05 am Post subject:

Jedi Knight

Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA

smdavies99 wrote:

lancelotlinc wrote:

keeping everything at GMT is not the answer to many system problems.

That's not my suggestion at all. My point is: all computational, machine-readable code processing should be accomplished in reference to GMT. This point has no relation to any system time clock setting.

Such is the case for all MQ messages. Any reference in the MQMD is related to GMT not local time.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER

Cogito-Ergo-Sum

Posted: Wed Nov 21, 2012 10:31 am Post subject:

Master

Joined: 07 Feb 2006
Posts: 293
Location: Bengaluru, India

fjb_saper wrote:

Didn't see whether the ESQL on Date Time has changed to take into consideration the offset at the time of the input vs at the time of execution...

In either case, the trace shows that the serialized output is not having timezone.

I thought, I will try a JavaCompute node sample to see if that would output the time zone information correctly. So, I had a look at the ESQL-to-Java datatype mappings table and see that the TIMESTAMP data type maps to the com.ibm.broker.plugin.MbTime with the following footnote:

Quote:

The time zone set in the Java variable is not important; you obtain the required time zone in the output ESQL.

I don't follow this. If the timezone was set in a JavaCompute node, would it not cascade over ? If it did,then, from the ESQL sample I posted above, the timezone would still not be serialized.

So, what is the conclusion ....
_________________
ALL opinions are welcome.
-----------------------------
Debugging tip:When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
---Sherlock Holmes

lancelotlinc

Posted: Wed Nov 21, 2012 11:14 am Post subject:

Jedi Knight

Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA

Cogito-Ergo-Sum wrote:

So, what is the conclusion ....

My conclusion is: machine code should strictly reference GMT only. Timezone is not needed: computers do not sleep.

The only time a TZ reference is needed is when a human needs a reference to his sleep schedule (ie. in the human user interface for application programs). The last time I checked, Enterprise Service Buses do not have human user application interfaces.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER

fjb_saper

Posted: Wed Nov 21, 2012 12:42 pm Post subject:

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY

Cogito-Ergo-Sum wrote:

Quote:

The time zone set in the Java variable is not important; you obtain the required time zone in the output ESQL.

time zone is only a parsing and formatting concept for Java.
The value itself would be the same regardless of TZ. (see Date(long)).
I guess that this means that the TZ is only a parsing / formatting concern in ESQL also and the actual value behind the scene is the same...

_________________
MQ & Broker admin

Cogito-Ergo-Sum

Posted: Wed Nov 21, 2012 5:51 pm Post subject:

Master

Joined: 07 Feb 2006
Posts: 293
Location: Bengaluru, India

Quote:

I guess that this means that the TZ is only a parsing / formatting concern in ESQL also and the actual value behind the scene is the same...

So, even though the timezone offset value disappears in serialized output the actual value hasn't altered ?

Code:

2012-11-20 03:51:39.926512 60 UserTrace BIP2539I: Node 'sample.SampleFlow.CMP01': Evaluating expression ''CAST(inputDateChar || 'T' || inputTimeChar || timezoneChar AS TIMESTAMP FORMAT 'YYYY-MM-dd'T'HH:mm:ssZZZ')'' at ('sample.SampleFlow_CMP01.Main', '23.38'). This resolved to ''CAST('2012-11-19T23:30:00-05:00' AS TIMESTAMP FORMAT 'YYYY-MM-dd'T'HH:mm:ssZZZ' )''. The result was ''TIMESTAMP '2012-11-19 23:30:00'''.

That is all well and good, but, if I do not pass the offset value, the generated XML document does not validate against the schema. Here is the portion of schema that fails the generated XML document.

Code:

So, I guess, what I am asking is, how do I generate time stamp value with time zone offset that is valid as per the schema portion shown above ?
_________________
ALL opinions are welcome.
-----------------------------
Debugging tip:When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
---Sherlock Holmes

Display posts from previous:

Goto page 1, 2, 3 Next

Page 1 of 3

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Unable to generate timestamp with timezone in Mapping node

Jump to:

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Protected by Anti-Spam ACP