Coming soon: Your brand new Help Center & Community! Get a sneak-peek here

Time Zone support

In this article

    A few universal truths:

    In many countries, TimeZone rules change from year to year and sometimes even within days!

     September 19, 2007


    Today, the government of Venezuela announced they were making their transition one day earlier (and 3 days away from this announcement) as they change their standard time by 30 minutes.

    Our system can handle this. The number 1 mechanism is a validFrom date field in all STD (Standard) and DST (Daylight Saving Time) rule records.

    Where do the rules come from and how will we manage rule changes over time?
    We have subscribed to WorldTime Server, Chaos Software Group, Inc. -, for our TimeZone data.
    STimeZoneManager has the necessary infrastructure to handle rule changes over time, even Base time zone changes!
    But the GUI for maintaining this is not yet implemented in SoAdmin.

    We have added a TimeZone feature to an existing system. We were committed to find ways of doing this with as little impact as possible. Most of the existing functionality should continue to work as if nothing had happened - if at all possible. Relevant questions were:

    Where and how do we plug this in to the system?

    As close to the GUI as possible. Add conversions directly in affected source files.

    What do we do with existing data when upgrading/activating the TimeZone feature?


    How do we store an Appointment's Start date and End date?

    In a TimeZone set by the administrator when activating the system. We call this the Base TimeZone. This is a prerequisite. If the TimeZoneManager cannot find a valid Base time zone, no conversions will occur! 

    Why do we position TimeZone convertions as close to the GUI as possible?

    In the windows client application we have a strict Model-View architecture with real-time update of all client views after any field changes.

    A change in TimeZone will affect some visual component, not the entire system. An Appointment Model instance can be viewed by several views, anyone of which might have a different TimeZone choice. It's that simple, it belongs in a display layer between the model and the view. 

    Why this Base Time Zone?

    From a TimeZone point of view, storing an appointment’s start~ and end~ dates in Base time is not such a good idea. It makes us sensitive to the quality of the time zone data and the noise that will occur there – this is scheduling of STD (Standard) and DST (Daylight Saving Time) rule changes.  An appointment could be unambiguously defined until 19.01.2038 by a combination of a Local SuperTime and a Geographical Location (a time zone location id or physical address that can be mapped to the correct time zone location id for the given date).  For the bulk of countries with already stable STD & DST rules, e.g. most, but not all, of Europe, there is no issue as the TimeZone data and system will be stable over time and reflect a constant mechanism. But for countries that have fluctuating rules, this becomes an issue as the introduction of a new STD and/or DST rule will invalidate all appointments that are already scheduled ahead of time and targeted to that time zone. Had we stored local time, this would not be the case. An error in a rule could simply be corrected and the scheduling of a new rule could go ahead regardless. Observe that only existing appointments targetted ahead of time are affected.

    Thus, when we add new STD and DST rules to a particular TimeZone, we will need to scan the data base in search of all appointments targetted to that particular timezone whose StartDate are >= the newly added rule's validFrom date and apply corrections. Had we stored Local time in the data base, we would not have to do anything.

    In SOCRM, once the TimeZone system has been activated, every appointment record must keep track of its target TimeZone - we cannot do without it and SOAdmin will depend on it when scheduling rule changes.

    Unfortunately, storing local time was never considered a practical option. I made various attempts to discuss this but to no avail, it never even made it to the table...

    The main reasons for storing Base times can be summarized as follows:

    A Base TimeZone allows an upgrade to position history so that the bulk of exisiting appointments will display correctly.
    As long as we internally in the system transport Base times only, everything else will continue to work as if nothing had happened - in theory anyway. To a large degree, this assumption was true but during the implementation it did show its ugly face many places causing many weeks of extra work.
    In SIX.Web, the database is used much more than in the Windows client. If all appointments in the data base are positioned relative to the same Base TimeZone, an sql select that depends on Start~ and/or End~ dates will produce comparable results else conversion to a common timezone would first have to be effectuated.
    This is however somewhat a weak argument. Granted we have areas where a time window is of essence, when populating our Diary Data Cache for example, but in order to hit all possible local-time candidates, all that is needed is to widen the time window by + 13 hours and - 11 hours and add post-filtering.

    Thus, internally all transport of an appointment’s start~ and end~ dates happens in Base time. A Base time is converted to a display time just prior to feeding it into a Control and converted back to base time right after retrieving it from a Control. Thus, we have positioned TimeZone conversion as close to the GUI as possible!

    Only an appointment start~ and end~ dates are directly affected by the timezone system. These are the crm5.appointment.do_by and crm5.appointment.endDate fields. Indirectly, crm5.appointment.activeDate is also affected. So expect these three fields to be in Base TimeZone.

    Any request made to the TimeZone System for a time zone conversion depends on exactly 3 parameters:

    1. A target Date&Time, a SuperTime.

    2. A target TimeZoneLocationId.

    3. The Base TimeZoneLocationId in effect at the target date.

    A TimeZoneLocationId is an invariable that points to a specific region of the globe. Example: Canada, Ontario (western) with code = CA-ON1. 

    Item 1, a target date is supplied by the client component requesting the conversion.

    Item 2, a location id, may be supplied by the client component but may also be wrapped in a current entity managed by the TimeZone system, typically the current Diary TimeZone Location Id.

    Item 3, the base location id, is mostly hidden from the system and always supplied by the TimeZone system itself.

    The target date is essential in a system that can handle changes in time zone rules over time. You don’t ask “what is the time offset between Rio de Janeiro and London” but “what is the time offset between Rio de Janeiro and London on the 15th April 2007 at 08.00 hrs. local (Rio) time”. 

    A time zone offset is the result of the GMT offset found for the target date applied to the time zone data model for the target location id minus the offset found for the now converted date, in GMT, applied to the time zone data model found for the base location id. [1]

    A time zone data model is a hierarchy of data needed to find the standard~ and/or the daylight saving time offset applicable to the target date.

    The resulting offset must then be applied to the target date in the correct direction:

                Going out: Increment      (Base -> Display)

                Going in: Decrement       (Display -> Base)


    Note that offset itself can be either positive or negative. Direction is subject to the bias used in the time zone data. As seen from GMT, going east is a positive offset, going west a negative offset. This is the method used in the data we purchased from World Time Server. Windows defines time zone offsets the other way around.