How to script the difference between time stamp

[#1]

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

2018-07-09 00:58:07.7030000

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

[#2 · Accepted solution]

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");

[#3]

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!

[#4]

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"
		)
	)
);

[#5]

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