Select Where Duration Is Greater Than

[#1]

Hello,

I'm trying to select rows where duration is greater than 1 sec. When I try to do this rows that show a duration of 1 sec are getting selected as well. What am I missing? Sample data table attached if needed.

Details of what I'm trying to accomplish

We have data that is recorded every second. I was trying to check if there were any gaps or periods that were not recorded.

Currently trying to complete this check for an entire year, over 25 million rows. Final table would have a start date/time, end date/time column and a column that says the duration of time that there was no data for.

Steps taken

1. Started with my column of Date&Time.
2. Created a second column with formula Lag(:Date&Time, -1) and called it EndDate&Time
3. Created a third column that took difference from EndDate&Time and Date&Time column and named it TimeDiff
4. Attempted to select where TimeDiff is greater than 0:00:01

-Row that have a value displayed of 0:00:01 are also being selected.

[#2]

The issue is that your display format is not displaying any decimal values for the duration.  Therefore, your value for TimeDiff for Row 1 is displayed as

     0:00:01

But if you specify in the format definition for the column to display 2 decimal points, you would see that Row 1's value is actually

     0:00:01.07

Which when you specify that you want to select all values greater than 1 second, 1.07 seconds is greater than 1 second, so it is selected.

[#3]

txnelson,

This helped me realize why the other rows were getting selected with my statement of select where TimeDiff >= 0:00:01. 

What I was really after was selecting TimeDiff >= 0:00:01.5. Had I had my display out to two decimal places I would have probably realized this sooner. 

Thank you!

[#4 · Accepted solution]

Hi @Herrera5238 

there are some issues with your formula. better to use the designated Duration formula which allows defining the units explicitly.

please see the attached file and open the column formulas.

[#5]

Ron,

I did come across this formula before posting but I was still running into issues when using select where.

However I tried it today with a blank slate and it worked! I must have clearly done something wrong when I first tried Date Difference().

I'm selecting this as solution because you've included the other columns that I was looking to get on my end result.

I appreciate you, txNelson and the JMP community you guys are awesome!

Thank You