ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker SupportBIP3204 cast char to time.

Post new topicReply to topic
BIP3204 cast char to time. View previous topic :: View next topic
Author Message
KIT_INC
PostPosted: Thu Apr 26, 2018 11:41 am Post subject: BIP3204 cast char to time. Reply with quote

Knight

Joined: 25 Aug 2006
Posts: 546

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
View user's profile Send private message
Vitor
PostPosted: Thu Apr 26, 2018 12:00 pm Post subject: Re: BIP3204 cast char to time. Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 25247
Location: Ohio, 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
View user's profile Send private message
fjb_saper
PostPosted: Thu Apr 26, 2018 5:31 pm Post subject: Re: BIP3204 cast char to time. Reply with quote

Grand Poobah

Joined: 18 Nov 2003
Posts: 19758
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
View user's profile Send private message Send e-mail
KIT_INC
PostPosted: Thu Apr 26, 2018 7:58 pm Post subject: Reply with quote

Knight

Joined: 25 Aug 2006
Posts: 546

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
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportBIP3204 cast char to time.
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


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.