| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Timpestamp formatting |
« View previous topic :: View next topic » |
| Author |
Message
|
| BCBS |
 Posted: Thu Jul 22, 2010 8:57 pm Post subject: Timpestamp formatting |
 |
|
Apprentice
Joined: 12 Jul 2006
Posts: 37
|
I am trying to convert input mesage's timestamp (ISO 8601) to Shanghai timezone specific.
Example:
Input (for instance, pst time, utc offset -7) : 2010-05-20T07:21:52.866-07:00
Output (must be shanghai timezone, utc offset +8 ): 2010-05-20T22:21:52.866+08:00
I know I can get this done using the below ESQL. But wondering if there is any simpler statement using cast/function to avoid string manipulation ESQL statements (last but 2/3 statements).
SET OutputRoot = InputRoot;
DECLARE inTime GMTTIMESTAMP;
DECLARE outTime GMTTIMESTAMP;
DECLARE inTimeChar CHARACTER;
DECLARE outTimeChar CHARACTER;
SET inTimeChar = InputRoot.XML.message.Timestamp;
SET inTime = CAST(inTimeChar AS GMTTIMESTAMP FORMAT 'IU');
SET outTime = EVAL('inTime + INTERVAL ''8'' HOUR');
SET outTimeChar = CAST(outTime AS CHARACTER FORMAT 'yyyy-MM-dd''T''HH:mm:ss');
SET outTimeChar = outTimeChar || '.000+08:00';
SET OutputRoot.XML.message.Timestamp = outTimeChar;
_________________
_________________________________ |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Thu Jul 22, 2010 10:05 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY
|
You'd probably be better off using a simple Java procedure taking input string as timestring, simple time format string, input time zone, output simple time format string and output time zone. The response would then be your formatted time string...
2010-05-20T21:21:52.866-07:00 => 2010-05-21T04:21:52.866+00:00
2010-05-20T22:21:52.866+08:00 => 2010-05-20T16:21:52.866+00:00
Notice that your calculation doesn't match...
2010-05-21T12:21:52.866+08:00 => 2010-05-21T04:21:52.866+00:00
As you add the offset from GMT to Local you have to subtract the offset from local to GMT => PST+7 = GMT and add the offset from GMT to local Shanghai = GMT +8, => +7 from PST to GMT and +8 from PST to Shanghai => Shanghai = PST +15
Have fun 
_________________
MQ & Broker admin
Last edited by fjb_saper on Fri Jul 23, 2010 6:56 am; edited 1 time in total |
|
| Back to top |
|
|
| flahunter |
| Posted: Thu Jul 22, 2010 10:46 pm Post subject: |
|
|
Acolyte
Joined: 30 Oct 2008
Posts: 62
|
| Quote: |
| simpler statement using cast/function to avoid string manipulation ESQL statements (last but 2/3 statements). |
If +08:00 is your Broker local timezone, you can code as below; if not, then I think there is no other easier way.
DECLARE inTime GMTTIMESTAMP;
DECLARE outTime TIMESTAMP;
DECLARE inTimeChar CHARACTER;
DECLARE outTimeChar CHARACTER;
SET inTimeChar = InputRoot.XML.message.Timestamp;
SET inTime = CAST(inTimeChar AS GMTTIMESTAMP FORMAT 'IU');
SET outTime = CAST(inTime AS TIMESTAMP); ==> the result value is source value plus the local time zone
SET outTimeChar = CAST(outTime AS CHARACTER FORMAT 'yyyy-MM-dd''T''HH:mm:ss ZZZ'); |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Jul 23, 2010 2:33 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| How does it fail to work if you just add an interval of 8 hours to the GMTTIMESTAMP? |
|
| Back to top |
|
|
| BCBS |
| Posted: Fri Jul 23, 2010 9:10 am Post subject: |
|
|
Apprentice
Joined: 12 Jul 2006
Posts: 37
|
| mqjeff wrote: |
| How does it fail to work if you just add an interval of 8 hours to the GMTTIMESTAMP? |
mqjeff, I didn't follow you, Is this question for me? My code is not failing, and its working the way I wanted the output.
| fjb_saper wrote: |
2010-05-20T21:21:52.866-07:00 => 2010-05-21T04:21:52.866+00:00
2010-05-20T22:21:52.866+08:00 => 2010-05-20T16:21:52.866+00:00
Notice that your calculation doesn't match...
2010-05-21T12:21:52.866+08:00 => 2010-05-21T04:21:52.866+00:00 |
fjb_saper, all I want to do is to represent the output time always specific to shanghai timezone. For example, if I get the time as '2010-05-20T07:21:52.866-07:00', then add 15 hours, and result should be '2010-05-20T22:21:52.866+08:00'. Second example, if I get input time as '2010-07-18T22:45:13.003-05:00', then add 13 hours, and result should be '2010-07-19T11:45:13.003+08:00'
My above ESQL is working good for my logic.
| fjb_saper wrote: |
| You'd probably be better off using a simple Java procedure taking input string as timestring, simple time format string, input time zone, output simple time format string and output time zone. The response would then be your formatted time string... |
This is an old flow which using ESQL for lot of other transformations. I just want to add this small logic in the same compute node. Not planning for a JCN.
| flahunter wrote: |
| If +08:00 is your Broker local timezone, you can code as below; if not, then I think there is no other easier way. |
NO, my local timezone is NOT+08:00
_________________
_________________________________ |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Jul 23, 2010 10:13 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| BCBS wrote: |
| mqjeff wrote: |
| How does it fail to work if you just add an interval of 8 hours to the GMTTIMESTAMP? |
mqjeff, I didn't follow you, Is this question for me? My code is not failing, and its working the way I wanted the output. |
The way I would solve this problem, of converting a timestamp from current timezone to GMT +8 timestamp, would be to convert the timestamp to GMTTIME and then add an INTERVAL of 8 hours.
I don't see any particular need to solve this problem by casting anything as character and then parsing it again. |
|
| Back to top |
|
|
| wonderland50 |
| Posted: Fri Mar 11, 2022 6:04 pm Post subject: Re: Timpestamp formatting |
|
|
Newbie
Joined: 11 Mar 2022
Posts: 1
|
| BCBS wrote: |
I am trying to convert input mesage's timestamp (ISO 8601) to Shanghai timezone specific.
Example:
Input (for instance, pst time, utc offset -7) : 2010-05-20T07:21:52.866-07:00
Output (must be shanghai timezone, utc offset +8 ): 2010-05-20T22:21:52.866+08:00
I know I can get this done using the below ESQL. But wondering if there is any simpler statement using cast/function to avoid string manipulation ESQL statements (last but 2/3 statements).
SET OutputRoot = InputRoot;
DECLARE inTime GMTTIMESTAMP;
DECLARE outTime GMTTIMESTAMP;
DECLARE inTimeChar CHARACTER;
DECLARE outTimeChar CHARACTER;
SET inTimeChar = InputRoot.XML.message.Timestamp;
SET inTime = CAST(inTimeChar AS GMTTIMESTAMP FORMAT 'IU');
SET outTime = EVAL('inTime + INTERVAL ''8'' HOUR');
SET outTimeChar = CAST(outTime AS CHARACTER FORMAT 'yyyy-MM-dd''T''HH:mm:ss');
SET outTimeChar = outTimeChar || '.000+08:00';
SET OutputRoot.XML.message.Timestamp = outTimeChar; |
Hi! Can I ask for help? I want to know why my code is not working? It still provide the ccyy-mm-dd-hh.mm.ss.mmmmmm
DECLARE inputStringDate CHARACTER FACTN.FACTN_TIMESTAMP;
DECLARE referralTimestamp GMTTIMESTAMP;
DECLARE patternInput GMTTIMESTAMP;
SET referralTimestamp = CAST(inputStringDate AS GMTTIMESTAMP FORMAT 'yyyy-mm-dd-hh.mm.ss'||'.000+08:00');
SET patternInput = CAST(referralTimestamp AS GMTTIMESTAMP FORMAT 'yyyymmdd'||'T'||'hhmmss'||'GMT');
SET policyTransactionDetailRef.ReferralTimestamp = patternInput; |
|
| Back to top |
|
|
| abhi_thri |
| Posted: Fri Mar 11, 2022 11:22 pm Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
Location: UK
|
hi...instead of reopening a decade old thread you are better off starting a new one with a clear problem description and may be link the old thread if approprate so.
If you are just attempting to add 8 hours look at the below comment,
| mqjeff wrote: |
The way I would solve this problem, of converting a timestamp from current timezone to GMT +8 timestamp, would be to convert the timestamp to GMTTIME and then add an INTERVAL of 8 hours.
|
Different formatting options can be found here,
https://www.ibm.com/docs/en/app-connect/11.0.0?topic=function-formatting-parsing-datetimes-as-strings |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
