TimeOffset variable

Document created by cdnadmin on Jan 24, 2014
Version 1Show Document
  • View in full screen mode
This document was generated from CDN thread

Created by: Paulo Silva on 13-04-2012 03:36:47 PM
I notice some of the existent report for Cisco Historical Reports Client use a TimeOffset variable on the stored procedure call; i can only guess HRC recognizes that and replaces it with the diference between local time and utc.
When i try to incorporate that in my own custom report i get and error saying Missing Variable Definition ($timeoffset).
I have looked at the xml definition files of the reports that use this variable and have found nothing special.
Any help/comment/insight appreciated!
Thanks in advance,
Paulo Silva

Subject: RE: TimeOffset variable
Replied by: Allister Purves on 19-04-2012 08:10:36 PM
I had the same problem and the only solution I found was to code around it. I removed the $timeoffset from the xml definition and the parameter list in the stored procedure.
Below is a test stored procedure that returns the offset to UTC in seconds as an integer. It's probably not the most elegant solution and someone may come up with a simpler way of doing it but it works :-)
DROP FUNCTION sp_test();
CREATE FUNCTION "uccxhruser".sp_test()
DEFINE l_eInterval INTERVAL SECOND(5) TO SECOND; -- This is a large enough for the greatest offset (13 hours = 46800 seconds)
DEFINE l_iInterval INT;
LET l_eEndDate = DBINFO('utc_to_datetime',0); -- In my case this returns '1970-01-01 10:00:00' as I am 10 hours ahead of UTC
LET l_eStartDate = '1970-01-01 00:00:00'; --UTC time
LET l_eInterval = l_eEndDate-l_eStartDate; --Gives the interval
LET l_iInterval = l_eInterval::Char(20)::INT; -- Converts to an integer which can then be used in the stored procedure in place of p_offset (variable used in the IVR Traffic Analysis Report)
RETURN l_iInterval;   
Hope this helps

Subject: RE: TimeOffset variable
Replied by: Paulo Silva on 15-06-2012 03:38:29 PM
Hi Allister,

Thanks for your reply!

Your procedure would return the offset between the informix server and UTC, not necessarily the offset between the client and UTC. Let me give you an example: when i ran your code in our environment i get 18000 or -5 hours, which is EST. However, MA is still in EDT, or -4 hours.

This is driving me nuts...

Thanks in advance!

Paulo Silva

Subject: RE: TimeOffset variable
Replied by: Paulo Silva on 20-06-2012 05:10:31 PM
Disregard this post...just testing something...


Paulo Silva