611789_en-US

liliconcom

Best way to convert UTC time format to numeric

Hi All,

 

 (JMP 17 & JMP 18EA)

 

  I have some XML data with a column that contains a time stamp, but the data format is not very compatible for what I'd like to do with it in JMP.

 

  The data is in the following UTC format yyyy-mm-ddThh:mm:ss.SSSZ, where the usual meanings are applied to the abbreviations. Here, there is actually a "T" after dd, and a Z after ".SSS", and the .SSS stands for milliseconds, there are no spaces. Unfortunately, JMP doesn't appear to have a built-in date format that can handle this well, even though it does have a similar format, but without the milliseconds and Z; so JMP can handle time in the format yyyy-mm-ddThh:mm:ss.

 

 The only way I can get JMP to parse the UTC date/time is by using the Substr() command in a column formula, remove the column formula, and then change the data type and format. This won't be too difficult to script, but just wondering if someone else has a more efficient way of handling date/time data in this unusual format.

 

  Or, does anyone know if JMP is considering to include this UTC time format to make conversion easier?

 

Thanks!,

DS

Consumer and Market ResearchRe: Best way to convert UTC time format to numeric

Hi @jthi ,

 

  Excellent, that is much more efficient at doing the conversion, thank you!

 

Thanks!,

DS

Re: Best way to convert UTC time format to numeric

You can get quite far by using Format Pattern with <-><-><'T'><::><::>Z

Names Default To Here(1);

date_str = "2022-12-23T14:54:50.123Z";

x = Informat(date_str,
 "Format Pattern",
 "<YYYY><-><MM><-><DL><DD><'T'><HH24><::><MM><::><SS>Z"
);

show(x, Second(x));
<P>(might have to do some rounding)</P></SS></MM></HH24></DD></DL></MM></YYYY>

Re: Best way to convert UTC time format to numeric

Thanks, saved the day, had the same issue and because I put the Z between a set of < > it didn't work.