topic Re: How to script the difference between time stamp in Discussions

How to script the difference between time stamp

Anonymous — Tue, 10 Jul 2018 20:34:08 GMT

Hello all,

I am working on a script that checks the missing data for users. We record data every 5 minutes. If the difference between 2 timestamps is larger than 5 minutes we define that there is missing data for that period of time. And the format of the time in my data table looks like this:

2018-07-09 00:58:07.7030000

Does anyone know how should I script to select the cell that has more than 5 minutes timestamps?

Thanks a lot,

Winnie

Re: How to script the difference between time stamp

txnelson — Tue, 10 Jul 2018 20:56:41 GMT

It appears that your date column is not a numeric, JMP Date/Time value. It appears that it is a character column. So the first thing you need to do is to convert it into a JMP Date/Time column. Once that is done, the way to check to see if the difference between values, is to use the Date Difference() function in conjunction with a Lag() Function.

If(Date Difference( lag(:DateTime),:DateTime,"Minute)>5,dataGood="Missing Data",dataGood="No Missing Data");

Re: How to script the difference between time stamp

Anonymous — Wed, 11 Jul 2018 13:56:29 GMT

Hi Jim,

Thank you so much for your reply. I have made changes based on your suggestion. My script is showing below:

dt2:ReadTime << Data Type( Numeric ) << Set Modeling Type( Continuous ) << Format( "yyyy-mm-ddThh:mm:ss", 19 );
If( Date Difference( Lag( :ReadTime ), :ReadTime, "mm" ) > 5,
	dataGood = "Missing Data",
	dataGood = "No Missing Data"
);

The data type and format have been changed successfully, however, I am confused about how to check dataGood. Do I need to create a new column for dataGood?

Thanks a lot!

Re: How to script the difference between time stamp

txnelson — Wed, 11 Jul 2018 13:50:48 GMT

Given the specifics you supplied, I think this might be a good solution for you

dt2:ReadTime << Data Type( Numeric ) << Set Modeling Type( Continuous )
 << Format( "yyyy-mm-ddThh:mm:ss", 19 );
dt2 << New Column( "Missing Data?",
	character,
	formula(
		If( Date Difference( Lag( :ReadTime ), :ReadTime, "Minute" ) > 5,
			"Missing Data",
			"No Missing Data"
		)
	)
);

Re: How to script the difference between time stamp

Anonymous — Wed, 11 Jul 2018 13:54:30 GMT

Hi Jim, I have figured out the issues. Thank you so much again!