topic Re: Manipulating Timestamp in Discussions

Manipulating Timestamp

corkman1987 — Thu, 08 Jun 2023 16:31:24 GMT

Hi I am struggling to convert a Timestamp into numeric format. I basically want to use the timestamp to create an Elapsed Time column. I am having difficultly achieving this with JMP16. Below is an example of the format. The change from 12:59 to 01:00 also seems to cause issues for me.

I was able to work with excel and manipulate the data to give me an Elapsed time in minutes (from row 1 as starting point). But I would prefer to use JMP and to create a script for it.

Thanks

Re: Manipulating Timestamp

txnelson — Mon, 06 Mar 2023 10:28:27 GMT

Here is a little script that will do the conversion.

Names Default To Here( 1 );
dt = Current Data Table();

// The comma in the time column's data does not fit into any
// standard JMP time formats, but if removed it does
For Each Row( :time = Substitute( :time, ",", " " ) );

// Convert to numeric and format it
:time<<data type(numeric)<<modeling type(continuous)<<Format( "Locale Date Time h:m:s", 23, 0 );

Re: Manipulating Timestamp

corkman1987 — Mon, 06 Mar 2023 10:55:09 GMT

Thanks for quick reply and helping with the format. I ran your script and also created and elapsed time column which is basically the following formula..

This gives me the elapsed time in seconds. However I have this issue when the timestamp moves from 12:59pm to 1pm. It is not picking up the AM/PM information. Therefore it thinks it is 01:00AM in error.

Re: Manipulating Timestamp

txnelson — Mon, 06 Mar 2023 11:13:42 GMT

Please attach a sample data table that has not yet been converted, and I will take a look at it.

Re: Manipulating Timestamp

corkman1987 — Mon, 06 Mar 2023 11:19:43 GMT

thanks - I am attaching an example.

Re: Manipulating Timestamp

txnelson — Mon, 06 Mar 2023 12:00:28 GMT

I found no error with the data table you supplied. It appears that your error occurred at row 3127. The table you supplied only has 1356 rows. I suspect that I need a sample of data that includes the error rows.

Additionally,

What OS are you using?

What JMP Version?

Re: Manipulating Timestamp

corkman1987 — Mon, 06 Mar 2023 12:57:01 GMT

I am still seeing the issue with the sample file i sent you. for example row 1052 onwards. I am using JMP 16 and Windows 10 OS.

Re: Manipulating Timestamp

mlo1 — Mon, 06 Mar 2023 14:30:38 GMT

I did the following in JMP 17 on MS win with the formula provided.
It worked on JMP16.2 on MS Win too.

Formulas are in the columns.

Re: Manipulating Timestamp

txnelson — Mon, 06 Mar 2023 14:34:35 GMT

I am not seeing the error. See the display below. I have also attached my data table. The Time 2 column is the same values as the time column, but with the display format set to display the actual numeric data values behind the date/time. It is the number of seconds since January 1, 1904. Time 2 has the formula :time but the format is changed to Fixed Dec, 20, 3.

I ran the code under both JMP 16 and 17, with the same results.

Re: Manipulating Timestamp

corkman1987 — Tue, 07 Mar 2023 11:07:35 GMT

Hi, thanks for sending this on to me. When i opened it all looked good. However i went into Time 2 and reapplied your formula and the results changed to what i had been seeing before. See below. The 13:00hrs data is changed to 01:00hrs.

I found the source of the issue. My PC settings are on United Kingdom Date and Time. When I restarted JMP after changing to US date and time settings - the formula is worked correct for me.

The formula will not work when my PC time is set for UK.

At least I know where the issue is coming from now. But still don't have a resolution unless I keep my PC settings on US.

Re: Manipulating Timestamp

txnelson — Tue, 07 Mar 2023 13:14:10 GMT

See if this works for you, if you keep your setting to UK.

Names Default To Here( 1 );
dt = Current Data Table();

dt << New Column( "time 2",
	Format( "m/d/y h:m:s", 23,3 ),
	set each value(
		date = Informat( Word( 1, :time, "," ) );

		hours = Num( Word( 2, :time, ",:" ) );
		If( hours == 12, hours = 0 );
		If( Word( -1, :time, " " ) == "PM",
			hours = hours + 12
		);

		:time 2 = date + In Hours( hours ) + In Minutes( Num( Word( 3, :time, ",:" ) ) )
		+Num( Word( 4, :time, ",: " ) );
	)
);

Additionally, I suggest that you submit your issue to JMP Support, so they can get it into their formal review of the issue.

support@jmp.com

Re: Manipulating Timestamp

corkman1987 — Tue, 07 Mar 2023 14:26:13 GMT

thank you very much for your help. your script is working well for me even when in UK date/time settings.