How to calculate up to Millisecond

Post Reply
Firefly007
Posts: 25
Joined: Tue Feb 26, 2013 12:51 am
OLAP Product: PA,PAW
Version: PA 2.0, PAW(2059)
Excel Version: Excel 2010

How to calculate up to Millisecond

Post by Firefly007 »

I was able to get value only up to seconds . can we calculate up to Milliseconds ?
V1= Now;
V2=TIMST(V1, ' \H\p \imin\ssec');
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: How to calculate up to Millisecond

Post by tomok »

What does the documentation say?
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Firefly007
Posts: 25
Joined: Tue Feb 26, 2013 12:51 am
OLAP Product: PA,PAW
Version: PA 2.0, PAW(2059)
Excel Version: Excel 2010

Re: How to calculate up to Millisecond

Post by Firefly007 »

In the documentation I could not find calculation upto Milliseconds. The least i was able to find only upto seconds.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: How to calculate up to Millisecond

Post by Alan Kirk »

Firefly007 wrote:In the documentation I could not find calculation upto Milliseconds. The least i was able to find only upto seconds.
Are you really talking about calculating to milliseconds, or displaying milliseconds?

What, in short, is your intent?

The TM1 system does go down to millisecond level as you can see from some of the performance manager cubes; whether you can rely on the accuracy of that is another matter.

Have you read this post on dates and times? If so, you should be aware that milliseconds are merely represented by decimal places way out to the right.

There is not (as far as I'm aware) a TM1 formatting code for converting them into a string showing milliseconds but it should be easy enough to work out how to do so when you understand how the values are stored. But again it depends on whether your intention is calculation or display.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Firefly007
Posts: 25
Joined: Tue Feb 26, 2013 12:51 am
OLAP Product: PA,PAW
Version: PA 2.0, PAW(2059)
Excel Version: Excel 2010

Re: How to calculate up to Millisecond

Post by Firefly007 »

I went through the link which you have provided. I still cannot figure out how to display upto milliseconds. I am trying to append the timestamp to an ascii output file.

This one displays only upto seconds as given in document.
V2=TIMST(V1, ' \H\p \imin\ssec');
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: How to calculate up to Millisecond

Post by Alan Kirk »

Firefly007 wrote:I went through the link which you have provided. I still cannot figure out how to display upto milliseconds. I am trying to append the timestamp to an ascii output file.

This one displays only upto seconds as given in document.
V2=TIMST(V1, ' \H\p \imin\ssec');
That does quite surprise me given that I had already said:
I wrote:There is not (as far as I'm aware) a TM1 formatting code for converting them into a string showing milliseconds
and then went on to add:
I wrote:but it should be easy enough to work out how to do so when you understand how the values are stored.
I'm sure that you took the time to carefully read the article that I referred you to, but it's possible that you missed one of the key points so allow me to elaborate.

A day is represented by the value 1. This is 24 hours, or 1,440 minutes, or 86,400 seconds, or 86,400,000 milliseconds.

If you were to take the decimal component of the value returned by Now() (that is, as the article explains, the time component of the value), you would have the decimal fraction of the day that has elapsed so far. This value can be manipulated in various ways.

1/24 represents the number of hours in a day, yes? So you could divide the decimal component of Now() by (1/24), then take the integer of that to get the number of completed hours so far in the day.

If you want to get the number of completed minutes since the start of the day instead, replace (1/24) with (1/1440).

If you want the number of completed minutes in the current hour, take the number of completed minutes, and subtract (the number of completed hours * 60).

Are you seeing the pattern?

It is possible that someone has already written the necessary code to extract the number of milliseconds, and that they will eventually post it which you can then use without any effort.

But in the absence of that, if you follow the calculation methods above you will be able to calculate down to the number of milliseconds for yourself, then just convert that value to a string.

Incidentally, if you are writing so many log files that they need to be separated down to the millisecond... you may well be adversely affecting TM1's performance. Writing to disk is (relatively) slow (certainly compared to in memory operations) and therefore something to be done sparingly. If you're doing it so often that a timestamp down to the second isn't sufficient, it may be worth rethinking what you're doing and why.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: How to calculate up to Millisecond

Post by rmackenzie »

Firefly007 wrote:I was able to get value only up to seconds . can we calculate up to Milliseconds ?
V1= Now;
V2=TIMST(V1, ' \H\p \imin\ssec');
It's also worth bearing in mind this nugget of information that Duncan posted about:
Duncan P wrote:Although NOW may return a number that looks very precise the granularity is in fact one second.
So although you may be able to get the current time at the precision of milliseconds, it may be a piece of information that you are not able to really use in a meaningful way. What are you trying to do with the timestamp?
Robin Mackenzie
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: How to calculate up to Millisecond

Post by Alan Kirk »

rmackenzie wrote:
Firefly007 wrote:I was able to get value only up to seconds . can we calculate up to Milliseconds ?
V1= Now;
V2=TIMST(V1, ' \H\p \imin\ssec');
It's also worth bearing in mind this nugget of information that Duncan posted about:
Duncan P wrote:Although NOW may return a number that looks very precise the granularity is in fact one second.
So although you may be able to get the current time at the precision of milliseconds, it may be a piece of information that you are not able to really use in a meaningful way.
Interesting; Duncan appears to be correct (not that I'd doubt it). But out of curiosity I ran some similar tests. The value returned certainly has the precision (1/86,400,000 of a day (1 millisecond) has its first significant digit at the 8th decimal place, and the Now() function returns far more than that), but the problem is the accuracy. (As I'd referred to in my first reply, but I didn't realise that it was as bad as this.) The value returned doesn't seem to be updated / read from the system clock in real time but only periodically; as Duncan observed, most likely each second or so. As a result you can be banging away retrieving timestamps constantly and get the same value for most of any given second.
rmackenzie wrote:What are you trying to do with the timestamp?
That was mentioned in their last reply:
Firefly007 wrote:I am trying to append the timestamp to an ascii output file.
As I said in my last one, if you need AsciiOutput timestamps down to the millisecond... the process that calls for that may be worth reviewing.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Firefly007
Posts: 25
Joined: Tue Feb 26, 2013 12:51 am
OLAP Product: PA,PAW
Version: PA 2.0, PAW(2059)
Excel Version: Excel 2010

Re: How to calculate up to Millisecond

Post by Firefly007 »

Sorry for responding late.

The purpose for this milliseconds was to check for flags files to save data to disk depending on the time stamp.I had four process which run in parallel.i was using test data which caused four process to complete almost same seconds so i was trying go up to milliseconds. how ever it was not the scenarios when i went with complete data set . for now I am good up to seconds. Thank for your help.
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: How to calculate up to Millisecond

Post by macsir »

Just want to share, now it is possible in Planning Analytics with function HierarchyTimeLastUpdated. It is not perfect as you have to create a dummy hierarchy to get millisecond. It would be nice to have a native function with millisecond format.

HierarchyTimeLastUpdated
This function indicates when a speci®ed dimension hierarchy was last updated.
The function returns a real number that represents the current day (including the hour, minute, second,
and millisecond) since the beginning of the year 1900.
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
Syntax
HierarchyTimeLastUpdated(dimension, hierarchy);
Argument Description
dimension The name of the dimension.
hierarchy The name of the hierarchy.
Example
HierarchyTimeLastUpdated('Region', 'Europe');
This example returns information on when the Europe hierarchy of the Region dimension was last
updated. If a value of 42548.<hours>.<minutes>.<milliseconds> is returned, you can divide 42548 by
365 to obtain (approximately) 116.
When added to the started of 1900, the result is a current year of
2016.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
ichermak
Posts: 4
Joined: Thu Feb 25, 2021 9:24 pm
OLAP Product: TM1 / IBM Planning Analytics
Version: 2.0.9.10
Excel Version: 2019
Contact:

Re: How to calculate up to Millisecond

Post by ichermak »

There seems to be a problem with the HierarchyTimeLastUpdated function, which does not return a correct value for hours.

Here is the test I performed:

Code: Select all

If(DimIx('}Clients', Tm1User) = 0); cTM1User = 'Chore'; Else; cTM1User = Tm1User; EndIf;
cStartTime = Now;
cProcessName = GetProcessName;
cIdExecution = cProcessName | '_' | TimSt(cStartTime, '\Y\m\d\h\i\s') | '_' | cTM1User  | '_' | Fill( '0', 5 - Long(NumberToString(Int(Rand * 65536)))) | NumberToString(Int(Rand * 65536));
cDebugFile = GetProcessErrorFileDirectory | cIdExecution | '.dbg';

nMax = 1000;
nIndex = 0;
While(nIndex <= nMax);
    nIndex = nIndex + 1;
    
    # Creation of temp hierarchy to use HierarchyTimeLastUpdated as it's the only way to get milliseconds for the acutal version of planning analytics
    cTempDimName = 'Dim_' | cIdExecution;
    cTempHierName = 'Hier_' | cIdExecution;
    If(DimensionExists('DimName') = 1);
        DimensionDestroy(cTempDimName);
    EndIf;
    DimensionCreate(cTempDimName);
    HierarchyCreate(cTempDimName, cTempHierName);
    
    # Need to subtract 21916 wich correspond to the serial date of 01/01/1960 starting from 01/01/1900 as date time function are based on serial date starting from 01/01/1960
    nDateTime = HierarchyTimeLastUpdated(cTempDimName, cTempHierName) - 21916;
    nNowDateTime = Now();
    
    DimensionDestroy(cTempDimName);
    
    nHour = TIMVL(nDateTime, 'H');
    nMinute = TIMVL(nDateTime, 'I');
    nSeconde = TIMVL(nDateTime, 'S');
    nMillisecond = 86400000 * (nDateTime - INT(nDateTime) - (nHour * (1 / 24)) - (nMinute * (1 / 1440)) - (nSeconde * (1 / 86400)));
    sMillisecond = NumberToString(nMillisecond);
    sTimeStamp = TimSt(nDateTime, '\Y-\m-\d \h:\i:\s') | '.' | sMillisecond;
    
    nNowHour = TIMVL(nNowDateTime, 'H');
    nNowMinute = TIMVL(nNowDateTime, 'I');
    nNowSeconde = TIMVL(nNowDateTime, 'S');
    nNowMillisecond = 86400000 * (nNowDateTime - INT(nNowDateTime) - (nNowHour * (1 / 24)) - (nNowMinute * (1 / 1440)) - (nNowSeconde * (1 / 86400)));
    sNowMillisecond = NumberToString(nNowMillisecond);
    sNowTimeStamp = TimSt(nNowDateTime, '\Y-\m-\d \h:\i:\s') | '.' | sNowMillisecond;
    
    ASCIIOutput(cDebugFile, sTimeStamp, sNowTimeStamp);
End;
Result:

Code: Select all

"2021-07-13 16:26:08.62,99989112","2021-07-13 18:26:08.0,000040047"
"2021-07-13 16:26:08.62,99989112","2021-07-13 18:26:08.0,000040047"
"2021-07-13 16:26:08.62,99989112","2021-07-13 18:26:08.0,000040047"
"2021-07-13 16:26:08.62,99989112","2021-07-13 18:26:08.0,000040047"
Do you guys see an error in my logic?

Thanks
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: How to calculate up to Millisecond

Post by declanr »

ichermak wrote: Tue Jul 13, 2021 4:44 pm There seems to be a problem with the HierarchyTimeLastUpdated function, which does not return a correct value for hours.

Here is the test I performed:

Code: Select all

If(DimIx('}Clients', Tm1User) = 0); cTM1User = 'Chore'; Else; cTM1User = Tm1User; EndIf;
cStartTime = Now;
cProcessName = GetProcessName;
cIdExecution = cProcessName | '_' | TimSt(cStartTime, '\Y\m\d\h\i\s') | '_' | cTM1User  | '_' | Fill( '0', 5 - Long(NumberToString(Int(Rand * 65536)))) | NumberToString(Int(Rand * 65536));
cDebugFile = GetProcessErrorFileDirectory | cIdExecution | '.dbg';

nMax = 1000;
nIndex = 0;
While(nIndex <= nMax);
    nIndex = nIndex + 1;
    
    # Creation of temp hierarchy to use HierarchyTimeLastUpdated as it's the only way to get milliseconds for the acutal version of planning analytics
    cTempDimName = 'Dim_' | cIdExecution;
    cTempHierName = 'Hier_' | cIdExecution;
    If(DimensionExists('DimName') = 1);
        DimensionDestroy(cTempDimName);
    EndIf;
    DimensionCreate(cTempDimName);
    HierarchyCreate(cTempDimName, cTempHierName);
    
    # Need to subtract 21916 wich correspond to the serial date of 01/01/1960 starting from 01/01/1900 as date time function are based on serial date starting from 01/01/1960
    nDateTime = HierarchyTimeLastUpdated(cTempDimName, cTempHierName) - 21916;
    nNowDateTime = Now();
    
    DimensionDestroy(cTempDimName);
    
    nHour = TIMVL(nDateTime, 'H');
    nMinute = TIMVL(nDateTime, 'I');
    nSeconde = TIMVL(nDateTime, 'S');
    nMillisecond = 86400000 * (nDateTime - INT(nDateTime) - (nHour * (1 / 24)) - (nMinute * (1 / 1440)) - (nSeconde * (1 / 86400)));
    sMillisecond = NumberToString(nMillisecond);
    sTimeStamp = TimSt(nDateTime, '\Y-\m-\d \h:\i:\s') | '.' | sMillisecond;
    
    nNowHour = TIMVL(nNowDateTime, 'H');
    nNowMinute = TIMVL(nNowDateTime, 'I');
    nNowSeconde = TIMVL(nNowDateTime, 'S');
    nNowMillisecond = 86400000 * (nNowDateTime - INT(nNowDateTime) - (nNowHour * (1 / 24)) - (nNowMinute * (1 / 1440)) - (nNowSeconde * (1 / 86400)));
    sNowMillisecond = NumberToString(nNowMillisecond);
    sNowTimeStamp = TimSt(nNowDateTime, '\Y-\m-\d \h:\i:\s') | '.' | sNowMillisecond;
    
    ASCIIOutput(cDebugFile, sTimeStamp, sNowTimeStamp);
End;
Result:

Code: Select all

"2021-07-13 16:26:08.62,99989112","2021-07-13 18:26:08.0,000040047"
"2021-07-13 16:26:08.62,99989112","2021-07-13 18:26:08.0,000040047"
"2021-07-13 16:26:08.62,99989112","2021-07-13 18:26:08.0,000040047"
"2021-07-13 16:26:08.62,99989112","2021-07-13 18:26:08.0,000040047"
Do you guys see an error in my logic?

Thanks
Not a function I have had any need to use before but looking at the 2 hour difference I would hazard a guess that it is returning GMT, which is the default logging time unless otherwise specified in the log properties. This only only holds true if the TM1 Server's OS is 2 hours out from GMT when you were running this test? e.g. most of Europe at the moment.
Declan Rodger
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: How to calculate up to Millisecond

Post by macsir »

Yes, I agree with declanr. DimensionTimeLastUpdated and HIERARCHYTIMELASTUPDATED need to be converted from GMT time to local time.

Code: Select all

#If converted to local time, need to add 10 / 24 for hour difference
vDateTimeWithMillisec = NUMBERTOSTRING((DimensionTimeLastUpdated(DimName)) + 10/24);
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
ichermak
Posts: 4
Joined: Thu Feb 25, 2021 9:24 pm
OLAP Product: TM1 / IBM Planning Analytics
Version: 2.0.9.10
Excel Version: 2019
Contact:

Re: How to calculate up to Millisecond

Post by ichermak »

Thank you for your answers!

It is indeed related to the fact that HierarchyTimeLastUpdated is based on GMT.

In my case, I think the best way to handel it is to use HierarchyTimeLastUpdated only to get the milliseconds part of a time.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: How to calculate up to Millisecond

Post by gtonkin »

Working in Milliseconds comes up every once in a while so thought that it may be useful to highlight the undocumented Millitime() function.

Code: Select all

nStart=Millitime();
Sleep(500);
nEnd=Millitime();
nDuration=nEnd-nStart;

asciioutput('debug.txt', NumberToString(nStart), NumberToString(nEnd), NumberToString(nDuration));
Output file contains:

Code: Select all

"73712166","73712676","510"
So there is a little bit of overhead in executing the sleep and setting the nEnd variable but the 500 millisecond delay can be seen.

Similarly, setting sleep to something higher like 54321 gives me:

Code: Select all

"73933351","73987683","54332"
HTH

P.S. I have not tried much to break the serial down to see what the seed is or how it relates to a normal date/time serial, if at all. Happy for someone to play and let us know.

Disclaimers: PAW does not know anything about Millitime and will not compile. As with all undocumented items, IBM will advise not to use in Production, if ever etc. etc. etc.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: How to calculate up to Millisecond

Post by lotsaram »

gtonkin wrote: Fri Dec 10, 2021 9:48 am Disclaimers: PAW does not know anything about Millitime and will not compile. As with all undocumented items, IBM will advise not to use in Production, if ever etc. etc. etc.
Well SLEEP and BREAK are also undocumented
so ¯\_(ツ)_/¯
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Duncan P
MVP
Posts: 600
Joined: Wed Aug 17, 2011 1:19 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2
Excel Version: 2003 2007
Location: York, UK

Re: How to calculate up to Millisecond

Post by Duncan P »

It's always worth pushing it to see what the tick granularity actually is, i.e. if you are hammering it continuously how often does the returned value actually change. That was how we found out how bad NOW is. Millitime certainly looks a lot better, but if you are using it for micro-benchmarking it's better to know for sure.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: How to calculate up to Millisecond

Post by gtonkin »

Duncan P wrote: Tue Dec 14, 2021 7:16 am It's always worth pushing it to see what the tick granularity actually is, i.e. if you are hammering it continuously how often does the returned value actually change...
Let us know what you find.

Also, for others, this function may be useful in combination with TM1User to create unique views, subsets etc.
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: How to calculate up to Millisecond

Post by Mark RMBC »

George, another function pulled from your magic hat! Thanks
Post Reply