- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
JSL: Select Rows Based On Timestamp
Seems like this should be relatively basic, but in JSL or even just JMP (select where dropdown), i'm unable to select rows based on specific timestamps in my time column (H:M:S)...do i have to change this to epoch?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JSL: Select Rows Based On Timestamp
The issue could be that you have your Time column setup as a date time column. A date time column measures the time in the number of seconds since 12AM January 1, 1904, while a time column has it's value stored as the number of seconds since midnight.
Here is an example:
I have two columns
Both columns are formatted using "h"m"s". However the first column is a date time column measured from 1904, and the time column is just a time column measured from 12AM today. This can be seen when the format on the 2 columns is changed to display the numeric values.
You need to check to see which type of column you have in order to determine how to setup the Select Where.
Another way, is to change the Select Where to a different selection.
current data table()<< Select Where(
hour(:datetime) == 14 & minute(:datetime) == 3 & second(:datetime) == 58);
// or
current data table()<< Select Where(
hour(:time) == 14 & minute(:time) == 3 & second(:time) == 58);
This structure will work for both types of columns
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JSL: Select Rows Based On Timestamp
Do the dates look right in the data table? Is the data type for this column numeric or character? Can you show us a sample of the data? Can you show us the JSL you have tried so far?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JSL: Select Rows Based On Timestamp
The time variable is going to be stored as the number of seconds since midnight. Therefore, using the syntax below will take the human readable time, and convert it to the number of seconds since midnight and then will do the comparison.
dt<<select where(:time == informat("1:13:32","h:m:s"));
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JSL: Select Rows Based On Timestamp
It's easier than that. You can use a time constant.
dt<<select where(:time == 01:13:32);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JSL: Select Rows Based On Timestamp
Check out Using dates, times, datetimes and durations in JMP.
You've not given us quite enough information about the situation but your final question about changing to epoch gives a clue. Yes, the right way to store dates, times, and durations is as a numeric epoch value. If the column is not numeric you can change it to numeric.
Here's an example where it is not numeric. Notice that the column is left justified. That's how character columns are shown.
Double-click at the top of the column to get to the Column Info and change the data type to Numeric, Modeling Type to Continuous and the Format to Time-> h:m:s. Check the Input Format. Make sure that matches your current values.
Click Okay and you'll have a numeric column (notice it's right justified).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JSL: Select Rows Based On Timestamp
apologies.
this is already in numeric, continuous with the format being time (h:m:s). but if you then try to select rows by condition, i get an error.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JSL: Select Rows Based On Timestamp
The issue could be that you have your Time column setup as a date time column. A date time column measures the time in the number of seconds since 12AM January 1, 1904, while a time column has it's value stored as the number of seconds since midnight.
Here is an example:
I have two columns
Both columns are formatted using "h"m"s". However the first column is a date time column measured from 1904, and the time column is just a time column measured from 12AM today. This can be seen when the format on the 2 columns is changed to display the numeric values.
You need to check to see which type of column you have in order to determine how to setup the Select Where.
Another way, is to change the Select Where to a different selection.
current data table()<< Select Where(
hour(:datetime) == 14 & minute(:datetime) == 3 & second(:datetime) == 58);
// or
current data table()<< Select Where(
hour(:time) == 14 & minute(:time) == 3 & second(:time) == 58);
This structure will work for both types of columns