cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Newbie2Jumpie
Level IV

Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

Hi JMP community,

Today I have a special challenge:

In JMP I have numeric entries (presumably OLE, German locale, in seconds). I know the corresponding correct values in EXCEL, e.g.

JMP                      EXCEL

43717,479872      09.09.2019 11:31:01

43717,479902      09.09.2019 11:31:04

43717,479884      09.09.2019 11:31:02

Could you show me a JSL way how to convert these JMP entries (in seconds) into the desired datetime format like shown in the EXCEL column? I tried several approaches but couldn't figure it out.

Thank you very much!

1 ACCEPTED SOLUTION

Accepted Solutions
Newbie2Jumpie
Level IV

Re: Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

My solution:

Tx' format approach:

format(in days(43717)-in days(1)-in years(4) + .479872*indays(1),"m/d/y h:m:s");

My computaion approach (assumes OLE timestring is character and
relevant info extracted into utility fields, not typo checked):

new column("My_OLE",numeric,"continuous",formula(format(in
days(:"OLE_days"-in days(1)-in years(:"OLE_years") +
:"OLE_hhmmss"*indays(1),"d/m/y h:m:s"))) ;

View solution in original post

8 REPLIES 8
txnelson
Super User

Re: Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

I don't know if this will help at all, but here is a brut force way of doing the conversion.  Note that the initial setting of the JMP date value is in U.S.  numeric structure

Names Default To Here( 1 );
JMPValue = 43717.479872;
// Add the number of seconds different from the JMPValue to the
// JMP date time value for 09.09.2019 11:31:01
JMPDateTime = JMPValue + 3650829743.52013;
// Display it's value using a standard JMP format
Show( Format( JMPDateTime, "d/m/y h:m:s" ) );
// Now show the same JMPDateTime but using a Custom
// format to change the value into exactly the desired
// structure

Show(
	Format(
		JMPDateTime,
		"Custom",
		Formula(
			Trim( Substr( " 0", -1 * Length( Char( Day( value ) ) ), 1) ) || Char( Day( value ) )
			|| "." ||
			Trim( Substr( " 0", -1 * Length( Char( Month( value ) ) ), 1) ) || Char( Month( value ) )
			|| "." ||
			Char( Year( value ) )
			|| " " ||
			Trim( Substr( " 0", -1 * Length( Char( Hour( value ) ) ), 1) ) || Char( Hour( value ) )
			|| ":" ||
			Trim( Substr( " 0", -1 * Length( Char( Minute( value ) ) ), 1) ) || Char( Minute( value ) )
			|| ":" ||
			Trim( Substr( " 0", -1 * Length( Char( Floor( Second( value ) ) ) ), 1) ) || Char( Floor( Second( value ) ) )
		),
		40
	)
);

Which yields the results

Format(JMPDateTime, "d/m/y h:m:s") = "09/09/2019 11:31:01 AM";
Format(JMPDateTime, "Custom", Formula(Trim(Substr(" 0", -1 * Length(Char(Day(value))), 1)) || Char(Day(value)) || "." || Trim(Substr(" 0", -1 * Length(Char(Month(value))), 1)) || Char(Month(value)) || "." || Char(Year(value)) || " " || Trim(Substr(" 0", -1 * Length(Char(Hour(value))), 1)) || Char(Hour(value)) || ":" || Trim(Substr(" 0", -1 * Length(Char(Minute(value))), 1)) || Char(Minute(value)) || ":" || Trim(Substr(" 0", -1 * Length(Char(Floor(Second(value)))), 1)) || Char(Floor(Second(value)))), 40) = "09.09.2019 11:31:01";

 

Jim
pmroz
Super User

Re: Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

Could you provide more information about the JMP values in seconds?  If I apply Jim's idea all three values are the same.

jmp_list = {43717.479872, 43717.479902, 43717.479884};
excel_list = {};
for (i = 1, i <= nitems(jmp_list), i++,
	one_dt = jmp_list[i];
// Add the number of seconds different from the JMPValue to the
// JMP date time value for 09.09.2019 11:31:01
	jmp_dt = one_dt + 3650829743.52013;
// Display its value using a standard JMP format
	dtc = Format( jmp_dt, "d/m/y h:m:s" ) ;

	dtc = substitute(dtc, "/", ".");
	excel_list[i] = dtc;
);
show(excel_list);

  

excel_list = {"09.09.2019 11:31:01 AM", "09.09.2019 11:31:01 AM", "09.09.2019 11:31:01 AM"};

The first two entries are 3 seconds apart, but the numbers are .000030 apart

The first and third entries are 1 second apart but the numbers are .000012 apart.

 

What are the units of the "JMP values in seconds" column?

Newbie2Jumpie
Level IV

Re: Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

Background is, this is a special OLE based datetime value. As far I was
told it seems to be seconds after 1900. EXCEL imports these values
correctly (sample values on the right). JMP doesn't.



Let's just simplify:

I don't mind whether the eventual JMP display format is US, international,
or European. Most important is a working conversion to a more
readable/common JMP datetime format. The EXCEL column shows what the
correct result should look like.



BTW, the two approaches don't work properly. Both set all entries to
09.09.2019 11:31:01.
txnelson
Super User

Re: Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

@Newbie2Jumpie 

You are correct......I took the wrong approach in trying to solve the issue.  The date values you have are actually Excel date/time values.  They are based upon the number of days since 01JAN1900, (43717), for the days, and a proportion of a day, (.479884), for the time and they start with day 1.  JMP base is the number of seconds since 01JAN1904 and starts at day 0.  So the conversion formula is:

in days(43717)-in days(1)-in years(4) + .479872*indays(1)

This can be displayed in the following JSL

format(in days(43717)-in days(1)-in years(4) + .479872*indays(1),"m/d/y h:m:s");

format(in days(43717)-in days(1)-in years(4) + .479902*indays(1),"m/d/y h:m:s");

format(in days(43717)-in days(1)-in years(4) + .479884*indays(1),"m/d/y h:m:s");
format(in days(43717)-in days(1)-in years(4) + .479872*indays(1),"m/d/y h:m:s")
/*:

"09/09/2019 11:31:01 AM"
//:*/
format(in days(43717)-in days(1)-in years(4) + .479902*indays(1),"m/d/y h:m:s");
/*:

"09/09/2019 11:31:04 AM"
//:*/
format(in days(43717)-in days(1)-in years(4) + .479884*indays(1),"m/d/y h:m:s");
/*:

"09/09/2019 11:31:02 AM"

 

Jim
Newbie2Jumpie
Level IV

Re: Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

My solution:

Tx' format approach:

format(in days(43717)-in days(1)-in years(4) + .479872*indays(1),"m/d/y h:m:s");

My computaion approach (assumes OLE timestring is character and
relevant info extracted into utility fields, not typo checked):

new column("My_OLE",numeric,"continuous",formula(format(in
days(:"OLE_days"-in days(1)-in years(:"OLE_years") +
:"OLE_hhmmss"*indays(1),"d/m/y h:m:s"))) ;

christianguhr
Level III

Re: Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

Hi @Newbie2Jumpie ,

in fact this is only true for summertime timestamps. In wintertime the timestamps you create are -1 hour back.

I have the exact same problem like you and I'm getting data from a balance to a excel spreedsheet in the OLE timeformat. 

I was frustrated because all of my data seemed to be shifted by 1 hour in the winter months.

 

@txnelson Is there a way (built-in JSL function maybe?)  to check if a timestamp is in summer or winter time and than add/substract one hour?

Obviously there are a lot of countries in the world having this feature in there system: https://www.timeanddate.com/time/dst/2022.html

so it should not only be a problem in Germany or Switzerland, where I am.

 

One can check if the timestamp is before the last sunday of march 2:00AM or after the last sunday of octover 3:00AM but this is kind of tricky, isn't it?

 

Best regards, 

Christian

jthi
Super User

Re: Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

Checking timezones generally is very very tricky and error prone The Problem with Time & Timezones - Computerphile (youtube.com) . To my knowledge there is no built-in function in JMP to check for current time zone.

 

There might be powershell/commandline commands to get the operating systems timezone and check for day light saving time. After that you could use Run Command() to run those commands and parse the correct offset and add that to JMP's datetime.

-Jarmo
christianguhr
Level III

Re: Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

Thanks @jthi for the Tom Scott Video: Perfect. 

 

There is something in the wishlist: https://community.jmp.com/t5/JMP-Wish-List/Time-Zone-Conversion/idi-p/484479

 

Maybe this will help us someday.

 

Greetings, Christian