Trouble with time separators

[#1]

We're having some difficulties with time formats when importing data.

In our lab, we have a pool of computers with either Win7 or Win10 with varying settings regarding time formats. This seems to cause issues when attempting to import data with timestamp to JMP12 and 14. Our timestamps have the format yyyy-mm-dd hh:mm:ss (e.g. 2018-08-24 13:39:27) and depending on which computer we test, the import either works or fails, because the time format has to use either a colon or a dot. How could we construct an import script that would work on all of the machines?

We are also wondering why there isn't a fixed time format in JMP that would remain the same dispite the settings in Windows? After all there is the ISO 8601 standard which defines colon as the separator.

[#2]

You could use a regular expression to match the parts fo either formatting option along with the character functions to produce a consistent JMP date-time value.

[#3]

Thanks for the suggestion! We have tried to modify the separators and that can be done quite easily, but we haven't figured out a way to detect which separators the JMP is expecting as, depending on the machine in question, they can be either a dot or a colon.

[#4]

That is why I suggested a regular expression. You could use "[\.:]" or "\.|:" as part of the expression for the presence of the separator. You don't need to pre-determine which format was used.

[#5]

I wonder if we're talking about the same thing, or there's a way to use regular expressions that I'm not familiar with. I'll try to explain our problem more clearly.

Our data has a consistent format (hh:mm:ss). There the separator is always a colon. E.g. the JMP on my computer works nicely with this format and I can easily import this data simply by selecting this format from the input format menu. When my colleague tries to do the same thing, the JMP on her computer does not recognize the data as time. On her computer the time format has to be hh.mm.ss (dot as a separator). Therefore in order to write a script for the import, that would work on both of these computers, we would have to first identify what is the time format expected by JMP on that particular machine.

Can regular expressions be used in the script to determine the input format? Currently we would have to have different scripts for different machines (one with colon and one with dot). 

Format( "y.m.d h:m:s", 22, 0 ), Informat( "y-m-d h:m:s", 0 )

Format( "y.m.d h.m.s", 22, 0 ), Informat( "y-m-d h.m.s", 0 )

Can we use this instead?

Format( "y.m.d h[\.:]m[\.:]s", 22, 0 ), Informat( "y-m-d h[\.:]m[\.:]s", 0 )

[#6]

Sorry for the confusion.

No, that way won't work as the arguments define a set format and may not include regular expressions. See my last post for another idea.

[#7 · Accepted solution]

Assuming that you import the time stamp as a character string into a data column called Date Time, a new numeric data column with this formula should result in the proper date regardless of the format used (of the two you described):

Parse Date(
	Regex( :Date Time,
		"(\d{4}-\d{2}-\d{2} \d{2})[:\.](\d{2})[:\.](\d{2})",
		"\1:\2:\3"
	)
);

[#8 · Accepted solution]

When I changed my Win10 time separator to "." and ran the following script below, it seemed to work OK, so I'm missing some detail.

 Regardless, here are a couple things that may be useful for your case.

• There is a JMP global preference under "Windows Specific" for honoring the system locale settings or not
• If you import the column as text and parse it later, the Parse Date() function has an Use Locale argument that you can turn off.

s = "time,
2018-08-24 13:39:27
2018-08-25 14:11:22
2018-08-26 16:33:44";
path = "$TEMP/times.csv";
Save Text File( path, s );
Open(path);

[#9]

Many thank for the workaround with the RegEx. With a quick test it seems to help us forward. I'll also go and do some testing with the "Windows spesific" setting when I can get an access to another computer. Hopefully that will be the universal solution for us.

Many thanks for all the help!

[#10]

It turns out that the setting suggested by XanGregg was exactly the one we were looking for. In case someone finds this conversation later, it seems that this setting can be changed also with the following script.

Set preferences( Use JMP Locale Settings( 1 ) );

I also ended up tweaking our input a bit with regex as markbailey suggested, so I'm also very grateful about this tip.