Re: Daylight Saving Time - Remedy vs. Crystal

[Blodgett, Jamie]

Title: OT: Daylight Saving Time - Remedy vs. Crystal

**

Remedy automatically translates your PCs timezone and all dates are handled as seconds since 1/1/1970 (I believe).  However, when you are working directly with Crystal, Crystal doesn't automatically adjust for the timezones.  If you are running a Crystal Report THRU Remedy, it normally functions like the client (seconds since...).

 

At least that's what I see on my reports.  To test, I usually create the Crystal report, then load it into the client and test again to ensure date calculations are correct.

 

Thanks,

Jamie

-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED]On Behalf Of Eric Cleereman (IT)
Sent: Thursday, May 04, 2006 4:07 PM
To: arslist@ARSLIST.ORG
Subject: OT: Daylight Saving Time - Remedy vs. Crystal

**

Hi All,

We're seeing issues with the way some of our custom Crystal Reports are calculating elapsed time, which seem related to Daylight Saving Time.

Both servers and the client are in EST5EDT.

Here's an example case where Remedy and Crystal disagree:
        Create Time:            4/2/2006 0:00:00 EST
        Resolve Time:           4/2/2006 12:00:00 EDT
        Time to Resolve:        12 hours (Crystal)
        Time to Resolve:        11 hours (Remedy)

Here's an example case where Remedy and Crystal agree:
        Create Time:            5/2/2006 0:00:00 EDT
        Resolve Time:           5/2/2006 12:00:00 EDT
        Time to Resolve:        12 hours (Crystal)
        Time to Resolve:        12 hours (Remedy)

In the example from 4/2, Remedy is right.  Only 11 hours elapsed, because at 1:59:59 the clocks advanced to 3:00:00, bypassing an hour.

Remedy is doing it's calculation based on Epoch time, which being based on GMT, does not change due to Daylight Saving Time.  Being that this is the case, I understand how Remedy is easily able to accurately determine the elapsed time.

Our Crystal developer is telling me that Crystal is not able to do calculations based on Epoch time, and that time returned to it is in the following format: MM/DD/YYYY HH:MM:SS.  As it sees the example from 4/2 as 4/2/2006 12:00:00 - 4/2/2006 0:00:00, it returns a result of 12 hours.

One method we are thinking about in order to get Crystal and Remedy concurring is:
 - Add an integer field to the forms being reported on.
 - Have Remedy set the value of that field to equal Time to Resolve in seconds.  (The workflow would fire when an entry is first moved to Resolved or directly to Closed)

 - A one time Escalation would set this for any entries which are already Resolved or Closed at the time we implement this.

 - Have Crystal pull from that field instead of doing the calculations for each individual entry.

I'm thinking the above approach should also allow the reports to run faster, since the majority of the math is already done before handing the data off to Crystal.

Has anyone else seen reporting issues because of Daylight Saving Time?  If so, I'd be interested in the following:
 - Did you do something to correct the issue, or are you just living with it?
 - If you corrected it, what approach worked best for you?

Eric Cleereman

__20060125_______________________This posting was submitted with HTML in it___

CONFIDENTIALITY: The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication, and the information contained in it, is strictly prohibited. If you are not the intended recipient, please contact the sender and immediately destroy all copies of the original message.
__20060125_______________________This posting was submitted with HTML in it___