| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| BIP3204 cast char to time. |
« View previous topic :: View next topic » |
| Author |
Message
|
| KIT_INC |
 Posted: Thu Apr 26, 2018 11:41 am Post subject: BIP3204 cast char to time. |
 |
|
Knight
Joined: 25 Aug 2006
Posts: 589
|
I am trying to get transaction time from broker monitoring message from IIB V10
The monitoring xml message has a
transaction.Start time of 2018-04-18T18:01:06.886402Z and a
transaction.end time of 2018-04-18T18:01:20.544476Z
I want to get transaction time by subtracting the start time from the end time.
Here is my test code
DECLARE TSC CHAR;
DECLARE TEC CHAR;
DECLARE TS TIME;
DECLARE TE TIME;
DECLARE pattern CHARACTER 'HH:mm:ss.ssssss';
DECLARE TI INTERVAL;
SET TS =18:01:06.886402;
SET TE =18:01:20.544476;
SET TS=CAST( TSC AS TIME FORMAT pattern);
SET TE=CAST( TEC AS TIME FORMAT pattern);
SET TI = (TE-TS) MINUTE TO SECOND;
I am getting error, debug shows:
RecoverableException
File F:\\build\\S1000_slot1\\S1000_P\\src\\CommonServices\\ImbTimeStampFormatter.cpp
Line 2022
Function ImbTimeStampFormatter::parseData
Type
Name
Label
Catalog BIPmsgs
Severity 3
Number 3204
Text subParse failed
Insert
Type 5
Text 18:01:06.886402
Insert
Type 5
Text HH:mm:ss.ssssss
Insert
Type 5
Text 886402
Insert
Type 5
Text ssssss
Can anyone help ? |
|
| Back to top |
|
 |
| Vitor |
| Posted: Thu Apr 26, 2018 12:00 pm Post subject: Re: BIP3204 cast char to time. |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| KIT_INC wrote: |
Here is my test code
DECLARE TSC CHAR;
DECLARE TEC CHAR;
DECLARE TS TIME;
DECLARE TE TIME;
DECLARE pattern CHARACTER 'HH:mm:ss.ssssss';
DECLARE TI INTERVAL;
SET TS =18:01:06.886402;
SET TE =18:01:20.544476;
SET TS=CAST( TSC AS TIME FORMAT pattern);
SET TE=CAST( TEC AS TIME FORMAT pattern);
SET TI = (TE-TS) MINUTE TO SECOND; |
Well:
- you don't indicate what TSC or TEC are set to in the above code
- if they're the timestamps from the event message (as I suspect), the pattern quoted doesn't match - it takes no account of the date portion, the "T" or the time zone marker ("Z"), which would be where your parse error is coming from.
Try something like (code untested, E&OE):
| Code: |
DECLARE TS TIMESTAMP;
DECLARE TE TIMESTAMP;
DECLARE TI INTERVAL;
SET TS = CAST(<whatever transaction.Start is) AS TIMESTAMP FORMAT 'IU');
SET TE = CAST(<whatever transaction.End is) AS TIMESTAMP FORMAT 'IU');
SET TI = (TE-TS) MINUTE TO SECOND;
|
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Apr 26, 2018 5:31 pm Post subject: Re: BIP3204 cast char to time. |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY
|
| KIT_INC wrote: |
I am trying to get transaction time from broker monitoring message from IIB V10
The monitoring xml message has a
transaction.Start time of 2018-04-18T18:01:06.886402Z and a
transaction.end time of 2018-04-18T18:01:20.544476Z
I want to get transaction time by subtracting the start time from the end time.
Here is my test code
DECLARE TSC CHAR;
DECLARE TEC CHAR;
DECLARE TS TIME;
DECLARE TE TIME;
DECLARE pattern CHARACTER 'HH:mm:ss.ssssss';
DECLARE TI INTERVAL;
Can anyone help ? |
Your exception tree should have given you the clue. Your pattern is wrong.
It should be
| Code: |
| DECLARE pattern CHARACTER 'yyyy-MM-dd''T''HH:mm:ss.SSSSSSZ'; |
Note how all the 's' letters behind the decimal point are in caps?
Hope it helps 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| KIT_INC |
| Posted: Thu Apr 26, 2018 7:58 pm Post subject: |
|
|
Knight
Joined: 25 Aug 2006
Posts: 589
|
Thanks fjb_saper,
I retest using your suggestion
DECLARE TSC CHAR;
DECLARE TEC CHAR;
DECLARE TS TIME ;
DECLARE TE TIME;
DECLARE T2 INTERVAL;
DECLARE pattern CHARACTER 'HH:mm:ss.SSSSSS';
SET TSC = '18:01:06.886402';
SET TEC = '18:01:20.544476';
SET TS = CAST(TSC AS TIME FORMAT pattern);
SET TE = CAST(TEC AS TIME FORMAT pattern);
SET T2 = (TE -TS) MINUTE TO SECOND;
This works the debug shows
TS 18:01:06.886
TE 18:01:20.544
TSC 18:01:06.886402
TEC 18:01:20.544476
T2 '00 00:00:13658074' DAY TO SECOND
I did another test using timestamp
DECLARE TSC CHAR;
DECLARE TEC CHAR;
DECLARE TSStamp TIMESTAMP;
DECLARE TEStamp TIMESTAMP;
DECLARE T1 INTERVAL;
SET TSC = '2018-04-18 18:01:06.886402';
SET TEC = '2018-04-18 18:01:20.544476';
SET TSStamp = CAST(TSC AS TIMESTAMP);
SET TEStamp = CAST(TEC AS TIMESTAMP);
SET T1 = (TEStamp -TSStamp) MINUTE TO SECOND;
This also works the debug shows
TSStamp 2018-04-18 18:01:06.886
TEStamp 2018-04-18 18:01:20.544
T1 '00 00:00:13658074' DAY TO SECOND
Both tests returns the same result for the interval
'00 00:00:13658074' DAY TO SECOND
I need some help on understand the result
1. My ESQL for T1 and T2 says 'MINUTE TO SECOND'. The broker changes it to DAY TO SECOND. what is the difference and what does it exactly means ?
2. I noticed when I cast the char to time or timestamp, the debug output of the second field only shows 3 digits after decimal (06.866 instead of 06.866402, 20.544 instead of 20544476). This may just be the way that it displays because the end result is good with 6 digits. |
|
| 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
|
|
|
|
