Dealing with daylight saving time and time zones can be challenging, but luckily, IBM Planning Analytics powered by TM1 has many features to help you overcome this situation.

In this article, you will learn how TM1 can help you with:

• Getting dates in different languages
• Calculating the time difference between two time zones
• Rescheduling your chores by one hour

The examples from this article can be run in your environment by downloading the HelloWorldTM1Date TurboIntegrator process:

Let’s first get more familiar with three important TurboIntegrator functions: FormatDate, ParseDate and NewDateFormatter.

The FormatDate function formats a date value according to a formater. If no formater is defined, the function will return the default format according to your location:

sNow = FormatDate( Now());

Apr 1, 2020

The second argument can be used to specify a format:

sNow = FormatDate( Now(), 'yyyy-MMMM-dd' );

2020-April-01

The ParseDate function parses a date string according to a formater and return a date value. To format the date value, the FormatDate function can be used:

sDateInput = '04-01-2020';nDateInput = ParseDate(sDateInput , 'MM-dd-yyyy');sDateOutput = FormatDate( nDateInput, 'dd MMMM yyyy'  );

01 April 2020

The NewDateFormatter defines a date formatter which can then be used by the FormatDate function.

The first argument is the Locale settings, by specifying en-au, the function will return the date in Australian format.

nUTCDateFormatId = NewDateFormatter('en-AU');sNow = FormatDate( Now(),'',nUTCDateFormatId);

01/04/2020

To get a time, you will need to specify datetime as the 5th argument:

nUTCDateFormatId = NewDateFormatter('en-AU','','','','datetime');sNow = FormatDate( Now(),'',nUTCDateFormatId);

01/04/2020 9:43:03 AM

The second argument is the timezone. The default value is Etc/UTC, you can specify any timezones from the Wikipedia list of time zones.

The third argument specifies if the times are treated as milliseconds since January 1, 1970 (unix) or TM1 serial format (serial).

nUTCDateFormatId = NewDateFormatter('en-AU', 'Etc/UTC', 'serial', '', 'datetime');sDatePattern = 'yyyy-MM-dd H:mm:dd';sNow = FormatDate( Now(), sDatePattern, nUTCDateFormatId );

2020-04-01 9:59:01

The fourth argument specifies the format style (short, medium, long, full).

sLocale = 'en-us';sFormatType = 'short';nNewFormatId = NewDateFormatter(sLocale, 'Etc/UTC', 'serial', sFormatType, 'datetime');sDate = FormatDate( Now(),'',nNewFormatId );

short 4/1/20 10:01 AM
medium Apr 1, 2020 10:01:59 AM
long April 1, 2020 10:01:59 AM GMT
full Wednesday, April 1, 2020 10:01:59 AM GMT

To get the same date in different languages, you will need to use the Locale argument. For example to get the date in French, use fr-FR:

nNewFormatId = NewDateFormatter('fr-FR', 'Etc/UTC', 'serial', 'full', 'datetime');sDate = FormatDate( Now(), '', nNewFormatId );

mercredi 1 avril 2020 10:01:59 UTC

Other examples of languages:

en-gb Wednesday, 1 April 2020 10:01:59 GMT
de-DE Mittwoch, 1. April 2020 10:01:59 GMT
no-no kl. 10:01:59 GMT onsdag 1. april 2020
nl-nl woensdag 1 april 2020 10:01:59 GMT
zu Lwesithathu 01 Apreli 2020 10:01:59 AM GMT

Now that you are more familiar with NewDateFormatter, ParseDate and FormatDate functions, let’s see how to calculate the time difference between two time zones.

In this example we are going to calculate the time difference between Sydney and London in three steps:

1. Convert the date in Sydney time
2. Convert Sydney time in London time
3. Calculate the difference

Let’s define the date source:

# Date to convertsDateSource = '2020-04-01 20:00:00 PM';

To get Sydney time, we need to define a NewDateFormatter for Sydney, then transform the date source into a Sydney date value with ParseDate and finally get the date string with FormatDate:

# Define Sydney Date Formatter nSydneyDateFormatId = NewDateFormatter('en-AU', 'Australia/Sydney', 'serial', 'medium', 'datetime');# Get the TimeValue For a specific datenSydneyDateTime = ParseDate(sDateSource , 'yyyy-MM-dd HH:mm:ss', nSydneyDateFormatId);# (re) Calculate the Sydney TimesDateFormatter = 'yyyy-MM-dd HH:mm:ss a z';sSydneyTime = FormatDate( nSydneyDateTime, sDateFormatter ,nSydneyDateFormatId );

To get London time, we will need a NewDateFormatter for London, then get the London time from the Sydney date value with FormatDate:

# Define London Time DateFormatter nLondonDateFormatId = NewDateFormatter('en-AU', 'Europe/London', 'serial', 'medium', 'datetime');# Get London time from nSydneyDateTimesLondonTime = FormatDate( nSydneyDateTime, sDateFormatter, nLondonDateFormatId  );

The last step is to convert the London date string into a date value with ParseDate and finally calculate the difference between the Sydney and London date values:

# Get London Date ValuenLondonDateTime = ParseDate(sLondonTime , nLondonDateFormatId);# Variance between Sydney Date Value and London Date ValuenDelta = nSydneyDateTime - nLondonDateTime;sDelta = FormatDate( nDelta, 'H');

Time difference Sydney vs London: 10 Hours

Rescheduling all chores by one hour can be achieved very easily with TM1py. The TM1py samples include one script called chore_reschedule_all_plus_one_hour.py. With just four lines of code, the script will loop through all chores and remove one hour:

with TM1Service(**config['tm1srv01']) as tm1:    # Get all chores. Loop through them and update them    for chore in tm1.chores.get_all():        chore.reschedule(hours=-1)        tm1.chores.update(chore)

This Python script can then be executed from a TM1 process by using the ExecuteCommand function:

pyExe = '"C:Python36python.exe"' ;pyFile = '"C:TM1pyTM1py-samplesAdminsitrationchore_reschedule_all_plus_one_hour.py"' ;EXECUTECOMMAND(pyExe |' '| pyFile,1);

IBM Planning Analytics (TM1 v11) introduced the option to set a chore in local time which should remove the need to reschedule all your chores twice per year when going on and off daylight savings.

• Happy TM1 users by pro-active support
• Optimize your TM1 license with Pulse
• Data Science with TM1