topic Re: Best way to convert UTC time format to numeric in Discussions

Best way to convert UTC time format to numeric

SDF1 — Thu, 08 Jun 2023 16:29:13 GMT

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!,

Re: Best way to convert UTC time format to numeric

jthi — Tue, 14 Mar 2023 14:49:31 GMT

You can get quite far by using Format Pattern with <YYYY><-><MM><-><DD><'T'><hh24><::><mm><::><ss>Z

Names Default To Here(1);

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

x = Informat(date_str,
	"Format Pattern",
	"<YYYY><-><MM><-><DD><'T'><hh24><::><mm><::><ss>Z"
);

show(x, Second(x));

(might have to do some rounding)

Re: Best way to convert UTC time format to numeric

SDF1 — Tue, 14 Mar 2023 15:18:38 GMT

Hi @jthi ,

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

Thanks!,

Re: Best way to convert UTC time format to numeric

Binyamin — Tue, 18 Feb 2025 12:01:51 GMT

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