PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 04/25/2012 10:08 PM by  Rod Weir
Database changes for v12 - UTC date conversion - VERY IMPORTANT!
 0 Replies
AddThis - Bookmarking and Sharing Button
Sort:
You are not authorized to post a reply.
Author Messages
Rod Weir
HelpMaster development team
Helpdesk expert
Helpdesk expert
Posts:517
Points:911


--
04/25/2012 10:08 PM

    Background

    HelpMaster v12 and above supports multiple timezones when entering, updating or viewing any date/time data.  This is very useful if you use HelpMaster across different timezones.

    In order to support this functionality, HelpMaster needs to store all date data in the database as UTC (Universal coordinated time).  When the date data is displayed to the end user, it is converted and displayed in local time.  If the timezone for the date is different to the local time, HelpMaster will indicate this.

    For all versions of HelpMaster prior to v12, the date data that has been entered into HelpMaster by each person in each location will be in their local time.   The date data displayed to each person will also be this time.  This can be confusing and misleading if you do not realize where each entry originated from.  A person viewing a date that was entered in a different timezone may not realize this.

    This is the issue that v12 addresses.

    By storing all date data in a common date timezone such as UTC, the timezone information and time offset (including daylight savings time) can be used to display date data in local time and preserve the original timezone data.

    Database upgrade and conversion

    In order to use HelpMaster v12, you will need to upgrade your database.  Use the HelpMaster database and reports wizard to automatically do this.  All of your existing HelpMaster data will be upgraded and all date data will be converted to UTC - nothing is lost.

    However!  It is very important to run the Database and Reports Wizard on a computer that has its' date timezone (Windows timezone) set to the same timezone that the majority of your date data is entered in.  This will allow the conversion process to convert your existing date data into UTC and preserve the same ratio of timezone information.

    For example.

    Suppose you have people using HelpMaster in 3 different timezones.

    • Timezone 1 = Wellington, New Zealand (+12)
    • Timezone 2 = Sydney, Australia (+10)
    • Timezone 3 = Perth, Australia (+8)

    The majority of your data entry is from the Sydney office.  This means that users in NZ, or Perth will be accustomed to viewing the majority of date data that is not in their timezone.

    The HelpMaster database upgrade to v12 will update all date data, however by running the conversion on a machine running in the same timezone as the majority of your date data, you preserve this same ratio of timezone differences for existing data only.  The effect for existing data will be business as usual - that is, the same potential ambiguity will exist.

    All new date data entry in HelpMaster v12 will be stored as UTC with timezone encoding and this ambiguity will no longer exist.

    Make a database backup and test!

    As usual, it is highly recommended to make a backup of your existing HelpMaster database before performing the database upgrade.  It is also recommended to view your converted data for any issues before converting your live production HelpMaster database.

    Date display in HelpMaster v12

    Once your HelpMaster database has been converted, all date data entry will be encoded with the originating timezone and v12 will store this data in the database in UTC.  When this date data is displayed, it will be converted back to a local timezone and all ambiguity will be removed.  Each person using HelpMaster will be able to tell where and when the date was entered, regardless of where it was entered.

    Custom reports

    If you have created custom reports that pull data from your HelpMaster database, you will need to update these to account for the changes in the date fields.  Remeber, all date fields in the database will now be in UTC.  You will need to convert these to your preferred timezone.

    For information about updating Crystal Reports, please refer to this discussion thread.

    Getting help, have questions?

    Please post any questions, or issues you have about this here.

    Thank you,
    The HelpMaster development team

    HelpMaster development team
    Check out the HelpMaster roadmap
    You are not authorized to post a reply.


    Configuration Management by Guy in Anything goes with 1 replies I looking people using Helpmaster for Configuration Management (CM) down to the Configuration Item (CI) level. How have you have built your CM tr...

    HelpMaster website update by Rod Weir in Announcements with 0 replies In preparation of the v17 release, the HelpMaster website has had a make-over. The new-look site provide additional information about the capabilit...

    Email tags not working. by David C in Email Manager with 1 replies Hi, I recently got the email service working and was poking around other areas (new to the software) and for some reason email template tags are not ...

    Knowledge Base improvements by Rod Weir in Wishlist with 0 replies We've recently received some requests to improve the knowledge base feature in HelpMaster via our support channel.  I'm re-posting some of the id...

    Triggered Events on Assets by Josh B in Priority Manager and Triggered Events with 2 replies I want to set up some automation for Assets but I need some help.  Rather than sending a support email through, I thought I'd share the developme...

    Automatically unsuspending jobs by Josh B in Priority Manager and Triggered Events with 2 replies Today I implemented some Priority Manager profiles to automate the unsuspending of jobs once a target date is reached.  Thought I'd share... ...

    Use PowerBI for HelpMaster dashboards by Rod Weir in Reports and reporting with 0 replies Microsoft PowerBI is a powerful dashboard tool that allows you to quickly create dashboards based on your helpdesk / service-desk data.  With an...

    Template catalogs by Rod Weir in HelpMaster development with 0 replies Another great new feature of v17 is "Template Catalogs".  Template catalogs are groupings of Job Templates and Knowledge Base articles.  E...

    Spotlight on workflow object - Log new job by Rod Weir in HelpMaster development with 4 replies The next version of HelpMaster has a radically improved workflow system with lots of new workflow objects.  One of the most powerful and useful i...

    Clients can now link to multiple sites by Rod Weir in HelpMaster development with 0 replies Another handy feature of HelpMaster v17 is the ability to link clients to multiple sites.  Previously clients could only be linked to 1 site only...

    RE: Configuration Management by Rod Weir in Anything goes Hi Guy, Once assets are in the system, they are generally linked to other entities (Clients, Sites, Knowledge Base articles, and even themselves).&...

    Configuration Management by Guy in Anything goes I looking people using Helpmaster for Configuration Management (CM) down to the Configuration Item (CI) level. How have you have built your CM tr...

    HelpMaster website update by Rod Weir in Announcements In preparation of the v17 release, the HelpMaster website has had a make-over. The new-look site provide additional information about the capabilit...

    RE: Spotlight on workflow object - Log new job by Rod Weir in HelpMaster development Hi Josh, Thanks again for the feedback and ideas. The new workflow designing has some of these things already.  Keyboard support has been i...

    RE: Spotlight on workflow object - Log new job by Josh B in HelpMaster development Couple more ideas for workflows. First, I want to point out that the pattern I have adopted is to have an message box present instructions then an act...

    RE: Email tags not working. by Rod Weir in Email Manager Hi David, Thanks for the information. Can you tell us what version of HelpMaster you're using, as well as what module is sending the email (Deskto...

    Email tags not working. by David C in Email Manager Hi, I recently got the email service working and was poking around other areas (new to the software) and for some reason email template tags are not ...

    RE: Spotlight on workflow object - Log new job by Rod Weir in HelpMaster development Hi Josh, Thanks for you post – some good ideas and feedback here as usual. Your idea of having a pre-job workflow is something we’ve alr...

    RE: Spotlight on workflow object - Log new job by Josh B in HelpMaster development Over the past couple of days I have been looking at workflows in v16.10.17.4 (without the changes mentioned above). We are attempting to map one of o...

    RE: How to improve SQL Server performance and thus HelpMaster's too. by Josh B in HelpMaster SQL Server database Thanks for the tip about business time. Makes total sense. Can you elaborate on anything we can do in terms of database maintenance, indexing or o...