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

[#1]

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!

[#2]

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";

[#3]

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?

[#4]

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.

[#5]

@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"

[#6 · Accepted solution]

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"))) ;

[#7]

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

[#8]

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.

[#9]

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