BookmarkSubscribeSubscribe to RSS Feed

How to script the difference between time stamp

Highlighted

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

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

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");
Jim
4 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

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

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

Super User

Joined:

Jun 22, 2012

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

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