338363_en-US

liliconcom

Convert character time to numeric continuous time

I have a dataset that the time column is in this format "Mon, 30 Nov 2020 21:42:26 GMT" and JMP recognizes that as a character data type. Is there a quick way to convert this column to numeric continuous type?

Data TableRe: Convert character time to numeric continuous time

Here is a little script that will create a new column with the converted value.  Please note that I am assuming your current column with the character date in it is called "Date".  Please change the script to what the real column's name is.

dt = Current Data Table();

dt << New Column( "Date Time",
 Format( "ddMonyyyy h:m:s", 22, 0 ),
 Input Format( "ddMonyyyy h:m:s", 0 ),
 ,
 formula(
  Informat(
   Word( 2, :Date, ", :" ) || Word( 3, :Date, ", :" ) ||
   Word( 4, :Date, ", :" ) || " " ||
   Word( 5, :Date, ", :" ) || ":" ||
   Word( 5, :Date, ", :" ) || ":" ||
   Word( 5, :Date, ", :" ),
   "ddMonyyy h:m:s"
  )
 )
);
dt:Date Time << delete formula;

Re: Convert character time to numeric continuous time

You can interactively make the change. Select the data column. Select Cols > Column Info. Change the Data Type to Numeric. Change the Format to the matching Date format of your character strings. You do not need a new column this way.

Re: Convert character time to numeric continuous time

If you use Mark's approach, you'll need to remove the extra bits first. I used a regex in the find/replace dialog:

You have to run it twice. The first time removes the day and the second the GMT.

The regex matches three letters followed by a comma and a space, or it matches a space followed by GMT.

 

Then use 

Change DataType *and* Format at the *same* time or you'll get missing values.

 

If you use Jim's approach, I think the 5-5-5 at the end should be 5-6-7.

 

Re: Convert character time to numeric continuous time

Thank you @Craige_Hales @markbailey @txnelson. Very helpful!!

Re: Convert character time to numeric continuous time

If @markbailey approach doesn't work, I use this addin by @brady_brady : Data Table Tools

Data Table Tools > Special Formula Columns > Custom Date Formula Writer.