4 Replies Latest reply on Aug 7, 2009 5:20 AM by Stu McNeill

    Time stamp out by 1 hour

    gramsay Specialist

      Every time I think about the way servicedesk handles time stamps my head hurts. However there are a couple of issues that seem to be unresolved.


      1. User created objects are an hour out from built in objects (in the summer). Getting round this involves some nightmarish crystal trickery

      2. User created objects have different time stamps depending on wether they are manually actioned or automatically actioned by the process. Again they are an hour out. I don't know of any way round this.

      3. Some times the "raise date" is used and other times the "create date" is used.


      All of this means there are errors in reports and discrepancies between the various window displays. (See attached screen shot for example).

      I've had a couple of support calls raised for this in the past but it's still there.


      Anybody else have these kind of issues?

        • 1. Re: Time stamp out by 1 hour

          I've had similar issues.  As a rule I only ever use Create date in reporting (I think raise date is when the incident is saved for the first time.  In my reports I use the following formula to compensate for daylight savings (this will only work up until 2012, but its quite obvious how to put in extra dates)



          if {pm_process.pm_creation_date} in CDateTime (2007, 03, 25, 01, 00, 00) to CDateTime (2007, 10, 28, 01, 00, 00)


          dateadd("h", 1, {pm_process.pm_creation_date})


          if {pm_process.pm_creation_date} in CDateTime (2008, 03, 30, 01, 00, 00) to CDateTime (2008, 10, 26, 01, 00, 00)


          dateadd("h", 1, {pm_process.pm_creation_date})


          if {pm_process.pm_creation_date} in CDateTime (2009, 03, 29, 01, 00, 00) to CDateTime (2009, 10, 25, 01, 00, 00)


          dateadd("h", 1, {pm_process.pm_creation_date})


          if {pm_process.pm_creation_date} in CDateTime (2010, 03, 28, 01, 00, 00) to CDateTime (2010, 10, 31, 01, 00, 00)


          dateadd("h", 1, {pm_process.pm_creation_date})


          if {pm_process.pm_creation_date} in CDateTime (2011, 03, 27, 01, 00, 00) to CDateTime (2011, 10, 30, 01, 00, 00)


          dateadd("h", 1, {pm_process.pm_creation_date})



          • 2. Re: Time stamp out by 1 hour
            karenpeacock SupportEmployee

            Hi Graham


            There are some articles which relate to this and I've put some links to these below which may be of interest.  However, you've made a very good point, and what I think would be good is a whitepaper or central article which brings these and / or any other questions about timezones and date/times altogether in one place.  I'll make a start and let you know when its all done - I might have to research some bits so don't expect it next week please


            If you have other more urgent questions not covered by the articles below or Bricktop's answer let us know.









            • 3. Re: Time stamp out by 1 hour
              gramsay Specialist

              I ran the script (restricting it to just datetimes) and it returns:


              Attributes with "md_adjust_for_tz = 0" are 192

              Attributes with "md_adjust_for_tz = 1" are 623


              How do I know which attributes can or should be changed?

              What will be the effect of doing so? (Will existing data be fixed or just new data?)


              When creating new datetime attributes how do I determine if it should be adjustable or not?

              How do I set this? (Currently it seems all new attributes are set to 0)


              Why are some times saved differently depending on wether the record is created manually or by the process?

              (See previous image for example - this was reported more than a year ago, ref 3213)


              Why are the time stamps on the audit trail/escalations/object windows all different?

              (It sounds trivial but it often results in a breached call because an analyst thought there were a couple of minutes to go when there wasn't)



              By the way this is the function I use to fix GMT/BST dates in Crystal



              Function (DateTimeVar gmtDateTime)
              //This function converts any GMT date-time to BST date-time after
              //checking whether the date is during BST or not.

              NumberVar i;
              DateVar searchDate;
              DateVar beginBST;
              DateVar endBST;
              NumberVar timeDiff;
              DateTimeVar localDateTime;

              //Find the BST begin date (which is always the last Sunday in March)
              //for the year of the "gmtDateTime" argument.
              For i := 31 To 1 Do
                searchDate := Date(Year(gmtDateTime), 03, i);
                If (DayOfWeek(searchDate) = 1) then Exit For
              beginBST := searchDate;

              //Find the BST end date (which is always the last Sunday in October)
              //for the year of the "gmtDateTime" argument.
              For i := 31 To 1 Step -1 Do
                searchDate := Date(Year(gmtDateTime), 10, i);
                If (DayOfWeek (searchDate) = 1) then Exit For
              endBST := searchDate;

              //The difference between GMT and BST is 1 hour
              If gmtDateTime in beginBST to endBST then timeDiff := -1 else timeDiff := 0;

              //Now convert GMT to BST
              localDateTime := DateAdd ("h", -timeDiff, gmtDateTime)


              • 4. Re: Time stamp out by 1 hour
                Stu McNeill Employee

                Hi Graham,


                Here is some information in regards to the Adjust for Time Zone (adjust_for_tz) property on attributes.


                You can safely change this from a 0 to a 1 on any user-generated attribute however I would not suggest a blanket change of all DateTime attribtues as there are some system ones that are required to be in local time rather than converted to UTC.  if the Name begins with an underscore though it should be fine.  As always try it on a test system first and take a full database backup...


                It has been a known niggle with dates for a long time but this has been addressed in verion 7.3 in three ways:


                1.  The Adjust for Time Zone property is now visible in Object Designer so you can choose whether to store in UTC or local time when creating attributes.

                2. This defaults to True (store in UTC) when creating attributes.

                3. A tool has been written (TZAdjuster) to change existing attributes to be in UTC and retrospectively change all historical data for the attribute based on a local timezone of your choice.


                With these above changes all user-generated DateTime attributes can now be in UTC.  In regards to the reporting on them there are some other documents on the community as Karen has mentioned for converting them back to local time which in itself can get complicated but at least if all dates are UTC to start with you have some consistency!  Personally I think the best way to handle the reporting is to use the 3rd party DLL to properly handle the conversion as this will take daylight savings into account.