Dates and times in TM1 - without all the hassle

  • Aug. 6, 2020

Introduction

In this article I would like to take the opportunity to show the usage of the new TM1 functions called NewDateFormatter(), ParseDate() and FormatDate(). If you do not recognize the name, it could be because you are not using TM1 10.2 or later. If you are in that boat, install that version to take advantage of what I discuss below (and of lots and lots more).

Dates and times

Using dates and times in TM1 has always been a pain in the backside. Yes, we have functions, there is documentation in the help files and there is a classic forumpost by Alan Kirk; please read this excellent article if you want to know more about Dayno(), Timst() and the like, including general information on dates and times in TM1 of obviously.

Converting dates to text or the other way round

Both Excel and TM1 store dates as a serial number, they use positive values to represent dates and times. Each day is equal to a value of 1, 6 AM in the morning is 0.25, 2 PM is equal to 14/24. Yet Excel and TM1 start counting with day 1 at a different day in history. For TM1 the (arbitrary) starting date is January 1, 1960 while for Excel this is January 1, 1900. The (Excel) serial number of today is 42322, while in TM1 this would be 20406. To know this you could effectively use: AsciiOutput( 'test.txt', NumberToString( Dayno(Today(0) )) ); Subtracting the smallest number from the largest one, the difference is 21916, which is exactly the number of days that elapsed between the starting date of either application. so please remember that 21916 because it will allow you to switch back and forth between dates as serial numbers in TM1 and Excel.

Now, let us move on to the topic of this article. In TM1 It is very cumbersome to translate the serial numbers for dates in TM1 to their textual representation. This is known as formatting a date. Equally difficult is to translate a textual representation of dates into a the serial number notation. The latter is known as parsing a date.

Examples:

LocaleOriginal representationReworked representation
en_US20406 (today's serial date)Saturday 14 November 2015
en_US7689 (the date of birth of a famous TM1 consultant)Monday 19 January 1981
nl_BEmaandag 19 januari 19817689
en_USNov 14, 2015 6:00:00 PM20406.75

    We will now look at 3 functions in Turbo Integrator to implement the conversions I talked about:
  • from serial numbers to text representations
  • from text representations to serial numbers

# As an example, just to get the message across, let us format the moment of "now"
i = NewDateFormatter('en_US', '', '', '', 'datetime' );
v_s = FormatDate( Now, 'EEEE dd MMMM y - kk:mm', i );
AsciiOutput( 'test.txt', v_s );

Yes, it is that easy (TM1 10.2 and later) to format a serial representation in a representation that human beings are able to understand. The output of the process above is (at least, now): 

Saturday 14 November 2015 - 20:45

I told TM1 to format the decimal number 20406,864988426 (the result of the Now() function) using the name of a date, the day number, the name of the month, the year, the hour and minutes.

The other way round is possible too and we use the ParseDate() function rather than the FormatDate() function: 

# As an example, just to get the message across, let us format the moment of "now"
i = NewDateFormatter('en_US', '', '', '', 'datetime' );
v = ParseDate( 'Saturday 14 November 2015 - 20:45', 'EEEE dd MMMM y - kk:mm', i );
AsciiOutput( 'test.txt', NumberToString( v ) );

The output of the process above is (not surprisingly of course): 

20406,864988426

Let us look at the ingredients of the 2 code blocks. First you need to call the NewDateFormatter() function (source). That way you can specify up to 5 settings:

  1. Locale: Locale used for parsing or formatting dates. If unspecified, the operating system locale is used.
  2. TimeZone: Timezone used for parsing or formatting dates. If unspecified, the time zone used is UTC ('Etc/UTC').
  3. UseUNIXTime: 'unix' or TM1 serial format (the default if unspecified).
  4. FormatterStyle: Controls the date format used when an empty pattern is specified to FormatDate or ParseDate. Valid values: 'full', 'long', 'medium', 'short'. If unspecified, 'medium' is chosen.
  5. FormatterType: Controls the type of format used when an empty pattern is specified to FormatDate or ParseDate. Valid values: 'time', 'date', 'datetime'. If unspecified, 'date' is chosen.

In the TI code above, we specify a UTC datetime for the en_US locale, for TM1 serial dates, medium length.

If no date formatter exists in your TI function, then a default formatter is used as though it had been created with the following call: 

i = NewDateFormatter('', 'Etc/UTC', 'serial', 'medium', 'date');

Next we have 2 similar functions, ParseDate() and FormatDate().

ParseDate() (source):

  1. DateString: as the name suggests, a date string.
  2. Pattern: the pattern used for parsing dates. If unspecified, the format is determined by the locale, FormatterStyle and FormatterType parameters from the NewDateFormatter function call.
  3. Index: index returned by a NewDateFormatter function call. No existing index means that the default index above is used.

FormatDate() (source):

  1. Date: a date value as a decimal number (TM1 serial number).
  2. Pattern: the pattern for formatting dates. If unspecified, the format is determined by the locale, FormatterStyle and FormatterType parameters from the NewDateFormatter function call.
  3. Index: index returned by a NewDateFormatter function call. No existing index means that the default index above is used.

Examples

As you can imagine, you can now do a lot of calculations with dates in TM1, without much coding or hassle. For instance, you load data into TM1 cubes based on the month (1 to 12) where the input format happens to contain text like Jan, Feb, Mar, …, Dec. I know that you can go a long way with (attributes on) dimensions, lookup cubes, and the existing date/time functions, but honestly that is nowhere as smooth as these new functions. We all know that regional settings on a PC might lead to serious issues when loading text files in TM1. I do not recommend it, but in a situation where different regional settings could lead to different text file formats, a parameter to a TI process could be for example the locale and based on that, ParseDate() knows how to handle the input (along with some other specifications, granted).

Generating a specific text output for dates and times, based on numeric representations, now becomes child's play too. For instance, you can "format" a date like the weekday (indeed, that function was absent in TM1). Or as the quarter. Or as the third Tuesday in the month, for instance. Or any of the - locale-dependent - translations for Monday, Tue, … we have worldwide in different languages. And the functions support different timezones and default formats. Nice stuff I would say. No more unnecessary attributes, functions/calculations, IF-ELSE-ENDIF structures in different languages, and so on.

In 1 TI process, you can have several different calls to the NewDateFormatter() function. That means that this stupid example works fine: 

i1 = NewDateFormatter('en_US', '', '', '', 'datetime' );
i2 = NewDateFormatter('nl_BE', '', '', '', 'datetime' );

v_s1 = FormatDate( Now, '', i1 );
v_s2 = FormatDate( Now, '', i2 );

AsciiOutput( 'test.txt', v_s1 );
AsciiOutput( 'test.txt', v_s2 );

The output of the process above is "now": 

Aug 6, 2020 12:52:17 AM
6-aug.-2020 00:52:17

Do you get a sense of what becomes possible in an easy way in TM1 Turbo Integrator processes (no rules though) ?

Last but not least, what is still missing in this article, are the possible valid values for each of the arguments. Here they are:

  1. Locale: overview
  2. TimeZone: overview
  3. FormatterStyle: 'full', 'long', 'medium', 'short'. If unspecified, 'medium' is chosen.
  4. FormatterType: 'time', 'date', 'datetime'. If unspecified, 'date' is chosen.
  5. Pattern: overview

Important caveat

Note that following the IBM documentation on the NewDateFormatter() function, only dates later than January 1, 1970 can be used with these functions…

Good luck with taking out all those unnecessary calculations from your TM1 models :-) Yes I look at you, Timst, Dayno, …




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links