nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to script the difference between time stamp

unknown

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

Accepted answers

txnelson

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

All comments

txnelson

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

unknown

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!

txnelson

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

unknown

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