Convert TM1 Contributor StateChangeDate to a formatted date

[cdredmond]

TM1 Contributor maintains the }tp_application_state}{AppId} cube to track the various "States" of approval of each individual node along the Approval Hierarchy. One of the elements in the }tp_node_info dimension is StateChangeDate. This appears to be a 13-digit number with no decimal. The TM1 serial date format is 5 significant digits followed by 6 decimals. I cannot find any part of the number which corrosponds to a proper TM1 or Excel serial date. Here is an example date which should corrolate to 2011/12/19 (within a day or two): 1323811982413.

I have also posted this on IBM developerWorks and LinkedIn.
Thank you for any help you can provide on this.

Christopher Redmond

[Michel Zijlema]

TM1 Contributor maintains the }tp_application_state}{AppId} cube to track the various "States" of approval of each individual node along the Approval Hierarchy. One of the elements in the }tp_node_info dimension is StateChangeDate. This appears to be a 13-digit number with no decimal. The TM1 serial date format is 5 significant digits followed by 6 decimals. I cannot find any part of the number which corrosponds to a proper TM1 or Excel serial date. Here is an example date which should corrolate to 2011/12/19 (within a day or two): 1323811982413.

I have also posted this on IBM developerWorks and LinkedIn.
Thank you for any help you can provide on this.

Christopher Redmond

Hi Christopher,

The number looks like a Unix timestamp. But when I convert this to a date on http://www.unixtimestamp.com, the number translates to 2001/12/13 ...

Michel

[skinners666]

I believe this is a java datetime format.

Below is a TI script that you can use to convert the dates to TM1 format. Looks lik eyou might be able to do it as a rule as well.

#****Begin: Generated Statements***
#****End: Generated Statements****


# java time value
raw = 1286815910343;
#days since 1.1.1970
days = raw /1000/60/60/24;
# TM1 uses 1.1.1960. So calculate offset to 1.1.1970 (which is 3653 days)
deltadays = DAYNO('1970-1-1');
asciioutput('d:\work\dates.txt', 'Delta to 1960:', STR(deltadays , 5, 0) );

# that makes in TM1 days
tm1days = days + deltadays;
asciioutput('d:\work\dates.txt', 'TM1Date:', DATE(tm1days, 1), 'tm1 raw= ' | STR(tm1days, 15, 10) );

#direct way
tm1time = raw / 86400000 + 3653;
asciioutput('d:\work\dates.txt', 'TM1 Serial:', STR(tm1time , 15, 10) );
asciioutput('d:\work\dates.txt', 'TM1Date(direct):', DATE( tm1time , 1 ) );
asciioutput('d:\work\dates.txt', 'TM1Date(direct):', TIMST( tm1time , '\M \D, \Y \h:\i:\s GMT' , 1 ) );

So the underlined formular should convert it.
This calculation does not cover timezone calculation, bit it returns the same value like
new Date(1286815910343).toString in Java

output of this TI is:

"Delta to 1960:"," 3653"
"TM1Date:","2010-10-11","tm1 raw= 18546.7026660069"
"TM1 Serial:","18546.7026660069"
"TM1Date(direct):","2010-10-11"
"TM1Date(direct):","OCT 11, 2010 16:51:50 GMT"

[cdredmond]

Michel and Skinners666,

Thank you both!
I have received some additional feedback from Jeff Allen, an IBM Solutions Specialist. He indicates this is a UNIX based date (java must use the same base). As such, he shares the same base formula this way when using Excel where H7 contains the 13 digit UNIX/Java serial date:

=CONCATENATE(TEXT(MONTH(INT((LEFT(H7,10))/(60*60*24))),0),"/",TEXT(DAY(INT((LEFT(H7,10))/(60*60*24))),0),"/",TEXT(YEAR(INT((LEFT(H7,10))/(60*60*24)))+70,0))

I came up with this formula using Excel to return a MS Serial Date where 86400 is the number of seconds in a day, 25569 is the offset between MS epoch (1/1/1900) and UNIX epoch (1/1/1970) and -8 is the time zone offset from GMT (in this case, Pacific):

=(INT(H7/1000)/86400)+25569+(-8/24)

I came up with the same TM1 to UNIX offset of 3,653 days.

Keep in mind a few key points when converting back and forth between these different date standards:
MS epoch is 1/1/1900: Serial date is 1.
TM1 epoch is 1/1/1960: Serial date is 0.
UNIX/Java epoch is 1/1/1970: Serial date is 0.

Thanks again both of you for the great input to help confirm these findings and the scraps of code I can use in my TI Process.

Merry Christmas and Happy New Year!

[failurehappening]

In a rule the following will create the timestamp (for Perth, GMT +8)

Timst(((numbr(DB('}tp_application_state}fa367afe-d397-4dbb-80ec-cf5ea7d17044',DIMNM(TABDIM ('}tp_application_state}fa367afe-d397-4dbb-80ec-cf5ea7d17044',1),1) ,'StateChangeDate'))/86400000)+3653+(8/24)), '\Y-\m-\d \h:\i')

So if the original is "1357880486452" the returned value is "2013-01-11 13:01"

[jimicron]

Hi,

I was wondering if anyone had an issue with the last code in this thread? I am using it and it works (converts the date/time into something more user friendly), however, it's not doing it as expected.

Here is my exact code:

Code: Select all

['Workflow State Measures':'State Change Date'] = S:
   TIMST(((NUMBR(DB('}tp_application_state}768207bb-5acf-467c-9a1c-90b10807e8cf', 
	DIMNM(TABDIM ('}tp_application_state}768207bb-5acf-467c-9a1c-90b10807e8cf',1),1) ,'StateChangeDate'))/86400000)+3653+(-7/24)), '\Y-\m-\d \h:\i');

But, as you can see in the screenshots, in the original tp_application_state cube, you see three different "StateChangeDate" numbers (should all have the same date, however, different times), but when converted using the rule given, they are all the same.

Do I need to do something in addition to the code that was given? Or any ideas why they are all the same once converted? Thanks a lot!

[jimicron]

bump

[declanr]

Code: Select all

['Workflow State Measures':'State Change Date'] = S:
   TIMST(((NUMBR(DB('}tp_application_state}768207bb-5acf-467c-9a1c-90b10807e8cf', 
	DIMNM(TABDIM ('}tp_application_state}768207bb-5acf-467c-9a1c-90b10807e8cf',1),1) ,'StateChangeDate'))/86400000)+3653+(-7/24)), '\Y-\m-\d \h:\i');

I will start by saying that I don't use contributor so I know very little about these cubes but at a quick glance of your code I can't see the part that relates to the "Organization" element; I am guessing that may be:

Code: Select all

DIMNM(TABDIM ('}tp_application_state}768207bb-5acf-467c-9a1c-90b10807e8cf',1),1)

If it is however; by doing a DimNm ( 'Dim', 1 ); you will always pull down the first element, hence everything would be the same.

If I have misunderstood the control cube structure (I'm not currently in a position to check) then you can just ignore that.

Cheers,
Declan

[jimicron]

Thanks Declanr.

I am just using the code that 'failurehappening' provided and simply replacing his 'tp' info with mine. It's working okay (converting) but like you said, it's repeating the same value for all items versus looking at each one individually and converting.

I am in need of a true conversion... meaning, the ones in yellow would be one date, the ones in red would be a different, and then the ones in purple would yet be another. Because, they truly are.

So, there must be something not quite right with that code and must need some slight tweaking. But, how to do that is where I am, unfortunately, stuck.

Thanks again to anyone!

JIM

[jimicron]

For trying to figure it out... I am going to replace the obnoxious name of the control cube with something shorter and more user friendly

Code: Select all

['Workflow State Measures':'State Change Date'] = S:
TIMST(((NUMBR(DB('WORKFLOW', DIMNM(TABDIM ('WORKFLOW',1),1) ,'StateChangeDate'))/86400000)+3653+(-7/24)), '\Y-\m-\d \h:\i');

[jimicron]

Ah HA!

That was the boost needed! Figured it out

Code: Select all

['Workflow State Measures':'State Change Date'] = S:
   IF (DB('}tp_application_state}768207bb-5acf-467c-9a1c-90b10807e8cf', !Organization, 'StateChangeDate') @='', '',
   TIMST(((NUMBR(DB('}tp_application_state}768207bb-5acf-467c-9a1c-90b10807e8cf',
   !Organization,'StateChangeDate'))/86400000)+3653+(-7/24)), '\Y-\m-\d \h:\i'));

I replaced that second part (that you called out) with the "Organization" name... saying, hey, apply this to all members of "Organization" by using the bang.

That worked!! Thank you as always!! Hopefully this additional info will help folks in the future that may look at this thread.

Here is a new screenshot showing how it's fixed:

[jimicron]

I'm gonna push my luck here

No big deal if there isn't a way, but I like to make things easy for the end-user... is there a way to make the time 12 hour versus 24 hours and put either an AM or a PM after the time?

Thus, it would look like this: 2014-03-03 10:33 AM

Just wondering... thanks a lot!!

[declanr]

I'm gonna push my luck here

No big deal if there isn't a way, but I like to make things easy for the end-user... is there a way to make the time 12 hour versus 24 hours and put either an AM or a PM after the time?

Thus, it would look like this: 2014-03-03 10:33 AM

Just wondering... thanks a lot!!

Alan Kirk has linked to an excellent time and dates section from the FAQ which you should look at but for quick reference you can just use \H:\i\p instead of \h:\i

[jimicron]

Thanks again Declanr! Awesome. It's working.

Also, thanks for pointing out the FAQ. I did not see that. However, I am going to post the link below because it has a lot of great info:

http://www.tm1forum.com/viewtopic.php?f=21&t=2441