Quick write-up on how to display time in different timezones in Planning Analytics while we’re waiting for a timezone aware format to be added.

We end up using a cube to display messages to end users in every project, something as simple as:

which is populated by a bog standard logMessage TI that you call to alert to interesting conditions. The goal is to expose configuration / mapping / input issues.

The time part of this is interesting whenever you have:

• server & users in different timezones (i.e. Planning Analytics on the cloud is UTC)
• users in different timezones because you want to display timestamp in users timezone, but you capture server timestamp during execution.

Here’s what I’m doing lately:

• Write server timestamp and convert to user time zone
  • CellPutN(Now()) in Server timestamp measure in the logMessage process.
  • Do a rule based conversion to show user timestamp like this (TM1User part makes it dynamic)

['User timestamp'] = N: 
	['Server timestamp'] +
      # offset of timezone by user
      DB('SYS User Parameter', TM1User(), 'Timezone offset, hours' ) \ 24;

• Assign default timezone to users or groups so that you can populate the user based offset parameter above
• Add a Timezone offset cube that calculates the difference between server timezone(UTC or wherever the box is setup) and a list of user timezones (pick the ones you need), with a TI like this:

# Update daily offset of hours to user timezones to cater for daylight savings
sLocale = CellGetS( 'Sys Parameter', 'Locale', 'text'  );
sServerTimezone =CellGetS( 'Sys Parameter', 'Server timezone', 'text'  );

nServerDateFormatter = NewDateFormatter(sLocale, sServerTimezone, 'serial', 'full', 'datetime');
sDateFormat = 'yyyy-MM-dd HH:mm:ss';
nNow = Now;
sServerTimeStamp = FormatDate(nNow, sDateFormat, nServerDateFormatter);

CellPutS('NO', '}CubeProperties', 'Sys Timezone', 'Logging');

CubeClearData( 'Sys Timezone' );

i = 1;
while (i<= DimSiz('Sys Timezone') );
    sTimezone = DimNm('Sys Timezone', i );
    nDateFormatter = NewDateFormatter(sLocale, sTimezone, 'serial', 'full', 'datetime');
    nLocalTime = ParseDate( sServerTimeStamp, sDateFormat, nDateFormatter );
    nTimeDifferenceHours =  ( nNow - nLocalTime  )  * 24;
    CellPutN(nTimeDifferenceHours, 'Sys Timezone',sTimezone, 'Offset from server, hours');
    i = i + 1;
end;

• Scheduling this TI daily means that you capture all the movements in timezones in this offset cube and time & dates will be reflected correctly in daylight savings