cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
The Discovery Summit 2025 Call for Content is open! Submit an abstract today to present at our premier analytics conference.
Choose Language Hide Translation Bar
View Original Published Thread

Best way to convert UTC time format to numeric

SDF1
Super User

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

1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

Re: Best way to convert UTC time format to numeric

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)

-Jarmo

View solution in original post

3 REPLIES 3
jthi
Super User

Re: Best way to convert UTC time format to numeric

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)

-Jarmo
SDF1
Super User


Re: 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

Binyamin
Level II

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.