| Author |
Message
|
| smeunier |
 Posted: Wed Jan 07, 2004 1:14 pm Post subject: Date Time milliseconds |
 |
|
Partisan
Joined: 19 Aug 2002
Posts: 305
Location: Green Mountains of Vermont
|
I would like to be able to produce a date/time format that is equivalent to the C funtion, which returns the number of milliseconds since epoc. Epoc is generally: Wed Dec 31 19:00:00 EST 1969, to the value of the function would need to be the number of millieseconds from then til now.
I'm attempting to put a message on a queue, which a legacy system expect in this format.
Any ideas? |
|
| Back to top |
|
 |
| kirani |
| Posted: Wed Jan 07, 2004 6:37 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
I don't think there is any direct ESQL function to do this. But, you can write few lines of ESQL code to get the value.
First, create a DATETIME variable with Dec 31 19:00:00 EST 1969 value, then subtract it from CURRENT_TIMESTAMP. You can get the result in seconds (INTERVAL datatype). You can then convert the seconds to miliseconds.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| smeunier |
| Posted: Thu Jan 08, 2004 8:17 pm Post subject: |
|
|
Partisan
Joined: 19 Aug 2002
Posts: 305
Location: Green Mountains of Vermont
|
Based on your suggestion, here's what id did:
| Code: |
DECLARE EpocTimeStamp TIMESTAMP;
DECLARE EventInterval INTERVAL;
SET EpocTimeStamp =TIMESTAMP '1970-01-01 00:00:00';
SET EventInterval = (CURRENT_TIMESTAMP - EpocTimeStamp) SECOND * 1000;
SET OutputRoot.XML.(XML.Element)EVENT.(XML.Element)LOG.(XML.Attribute)TIMESTAMP= CAST(EventInterval AS CHARACTER);
|
Here's what I got(I need to have milliseconds in a string as an xml attribute:
| Code: |
<LOG ID="Z_SHIPMENT" LEVEL="E" TIMESTAMP="INTERVAL INTERVAL '-155676494' SECOND"/>
|
|
|
| Back to top |
|
|
| kirani |
| Posted: Thu Jan 08, 2004 9:55 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Try this code,
| Code: |
DECLARE EpocTimeStamp TIMESTAMP;
DECLARE EventInterval INTERVAL;
SET EpocTimeStamp = TIMESTAMP '1970-01-01 00:00:00';
SET EventInterval = (CURRENT_TIMESTAMP - EpocTimeStamp) SECOND * 1000;
SET OutputRoot.XML.(XML.Element)"EVENT".(XML.Element)LOG.(XML.Attribute)"TIMESTAMP" = CAST(EventInterval AS INT);
|
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| kirani |
| Posted: Fri Jan 09, 2004 1:59 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
I tested above code on my machine and here is the output.
| Quote: |
<EVENT><LOG TIMESTAMP="1073656475747"/></EVENT>
|
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| wooda |
| Posted: Thu Jan 15, 2004 1:46 am Post subject: |
|
|
Master
Joined: 21 Nov 2003
Posts: 265
Location: UK
|
If you output in MRM CWF (which I assume you want to do anyway as you are modeling a legacy C format) then you can just model your field as a DATETIME. Set the Physical Type CWF property to "Time MilliSeconds" this will output in the format you are looking for.
You can then create the field in ESQL as a normal timestamp specifying the date and time in the normal way.
Or if this data is coming from an input message then if you model the input as datatime too you can map it straight to your output field and the CWF parser will convert your datetime into Time Milliseconds format. |
|
| Back to top |
|
|
| smeunier |
| Posted: Wed Jul 21, 2004 1:13 pm Post subject: |
|
|
Partisan
Joined: 19 Aug 2002
Posts: 305
Location: Green Mountains of Vermont
|
After implementing this and just getting back to it, I noticed, that the resolved time is 4 hrs off. I looked back through a trace, tring to figure where this could be occuring, but did not find anything concrete. Initially, I thought that the problems was because the TIMESTAMP was picking up GMT. But upon trace investigation, the output looks correct. So I'm wondering, where the 4 hrs are going?
Below is sample output of trace. Any ideas/help would be great. I need those 4 hours!
| Code: |
Executing statement 'SET EpocTimeStamp = TIMESTAMP '1970-01-01 00:00:00';' at (10, 1).
2004-06-29 23:08:54.451999 1092 UserTrace BIP2537I: Node 'MD_SAP MessageFlow SiView Z_WIP_SHIPPED_OUT_AK Message Handler.MD_SAP.SubFlow.Z_WIP_SHIPPED_OUT_AK BO Transformer1.MD_SAP SubFlow Log Message Handler1.Write Event Message': Executing statement 'SET EventInterval = CURRENT_TIMESTAMP - EpocTimeStamp * 1000;' at (11, 1).
2004-06-29 23:08:54.451999 1092 UserTrace BIP2538I: Node 'MD_SAP MessageFlow SiView Z_WIP_SHIPPED_OUT_AK Message Handler.MD_SAP.SubFlow.Z_WIP_SHIPPED_OUT_AK BO Transformer1.MD_SAP SubFlow Log Message Handler1.Write Event Message': Evaluating expression 'CURRENT_TIMESTAMP - EpocTimeStamp * 1000' at (11, 64).
2004-06-29 23:08:54.451999 1092 UserTrace BIP2538I: Node 'MD_SAP MessageFlow SiView Z_WIP_SHIPPED_OUT_AK Message Handler.MD_SAP.SubFlow.Z_WIP_SHIPPED_OUT_AK BO Transformer1.MD_SAP SubFlow Log Message Handler1.Write Event Message': Evaluating expression 'CURRENT_TIMESTAMP - EpocTimeStamp' at (11, 40).
2004-06-29 23:08:54.451999 1092 UserTrace BIP2538I: Node 'MD_SAP MessageFlow SiView Z_WIP_SHIPPED_OUT_AK Message Handler.MD_SAP.SubFlow.Z_WIP_SHIPPED_OUT_AK BO Transformer1.MD_SAP SubFlow Log Message Handler1.Write Event Message': Evaluating expression 'CURRENT_TIMESTAMP' at (11, 22).
2004-06-29 23:08:54.451999 1092 UserTrace BIP2540I: Node 'MD_SAP MessageFlow SiView Z_WIP_SHIPPED_OUT_AK Message Handler.MD_SAP.SubFlow.Z_WIP_SHIPPED_OUT_AK BO Transformer1.MD_SAP SubFlow Log Message Handler1.Write Event Message': Finished evaluating expression 'CURRENT_TIMESTAMP' at (11, 22). The result was 'TIMESTAMP '2004-06-29 23:08:54.452''.
2004-06-29 23:08:54.451999 1092 UserTrace BIP2538I: Node 'MD_SAP MessageFlow SiView Z_WIP_SHIPPED_OUT_AK Message Handler.MD_SAP.SubFlow.Z_WIP_SHIPPED_OUT_AK BO Transformer1.MD_SAP SubFlow Log Message Handler1.Write Event Message': Evaluating expression 'EpocTimeStamp' at (11, 42).
2004-06-29 23:08:54.451999 1092 UserTrace BIP2539I: Node 'MD_SAP MessageFlow SiView Z_WIP_SHIPPED_OUT_AK Message Handler.MD_SAP.SubFlow.Z_WIP_SHIPPED_OUT_AK BO Transformer1.MD_SAP SubFlow Log Message Handler1.Write Event Message': Finished evaluating expression 'CURRENT_TIMESTAMP - EpocTimeStamp' at (11, 40). This resolved to 'TIMESTAMP '2004-06-29 23:08:54.452' - TIMESTAMP '1970-01-01 00:00:00''. The result was 'INTERVAL INTERVAL '1088550534.452' SECOND'.
2004-06-29 23:08:54.462001 1092 UserTrace BIP2539I: Node 'MD_SAP MessageFlow SiView Z_WIP_SHIPPED_OUT_AK Message Handler.MD_SAP.SubFlow.Z_WIP_SHIPPED_OUT_AK BO Transformer1.MD_SAP SubFlow Log Message Handler1.Write Event Message': Finished evaluating expression 'CURRENT_TIMESTAMP - EpocTimeStamp * 1000' at (11, 64). [color=red]This resolved to 'INTERVAL INTERVAL '1088550534.452' SECOND * 1000'. The result was 'INTERVAL INTERVAL '1923808564' SECOND'. [/color] |
After cast to Integer
| Code: |
2004-06-29 23:08:54.472000 1092 UserTrace BIP2539I: Node 'MD_SAP MessageFlow SiView Z_WIP_SHIPPED_OUT_AK Message Handler.MD_SAP.SubFlow.Z_WIP_SHIPPED_OUT_AK BO Transformer1.MD_SAP SubFlow Log Message Handler1.Write Event Message': Finished evaluating expression 'CAST(EventInterval AS INTEGER)' at (38, 82). This resolved to [color=red]'CAST(INTERVAL INTERVAL '1923808564' SECOND AS INTEGER )'. The result was '1088550534452'. [/color] |
|
|
| Back to top |
|
|
| PGoodhart |
| Posted: Mon Aug 23, 2004 7:11 am Post subject: |
|
|
Master
Joined: 17 Jun 2004
Posts: 278
Location: Harrisburg PA
|
I believe I have your answer.
I had a problem with date/time myself not to long ago involving validation.
According to IBM support they do NOT respect timezones in datetime math, so if you are specifing it in the datetime you may be getting switched to the local server timezone (which may be set to GMT? fairly common on unix/sun/linux) without your input. Generally the datetime math/validation in the broker is off/broken/subfunctional. There are some fixes out there depending on your system/broker level. I'd put in a ticket with support at this point.
_________________
Patrick Goodhart
MQ Admin/Web Developer/Consultant
WebSphere Application Server Admin |
|
| Back to top |
|
|
| smeunier |
| Posted: Tue Aug 24, 2004 5:25 am Post subject: |
|
|
Partisan
Joined: 19 Aug 2002
Posts: 305
Location: Green Mountains of Vermont
|
| My thought was to switch to GMTTIMESTAMP and use the TIMeZONE function to add/subtract the hours for resolving to the local date/time. Sound doable? |
|
| Back to top |
|
|
|
|
