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?

Advanced Statistical ModelingMass CustomizationRe: Convert character time to numeric continuous time

This should also work (in a new column formula), since for the format you've provided, there are always 5 leading and 4 trailing characters to remove. As above, you can format as desired afterward by using the column property dialog.

 

Note: In the formula below, the source column's name is C1; please change as is appropriate for your table.

 

Informat( Substr( :C1, 6, Length( :C1 ) - 9 ) )

 

 

Re: Convert character time to numeric continuous timeThanks! yes, I have used that add-in and it is really helpful.
Re: Convert character time to numeric continuous time

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

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

Re: Convert character time to numeric continuous time

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

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.You have to run it twice. The first time removes the day and the second the GMT.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.Change DataType *and* Format at the *same* time or you'll get missing values.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

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

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;