TimeOffset variable

Version 1
    This document was generated from CDN thread

    Created by: Paulo Silva on 13-04-2012 03:36:47 PM
    Hello,
     
    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
    Hi,
     
    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()
    RETURNING  INT AS Start_Time;
     
    DEFINE l_eStartDate, l_eEndDate DATETIME YEAR TO FRACTION(3);
    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;   
     
    END FUNCTION;
     
    Hope this helps
     
    Allister

    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...


    Thanks,

    Paulo Silva