topic Re: JSL: Select Rows Based On Timestamp in Discussions

JSL: Select Rows Based On Timestamp

haleyhoewt — Sun, 11 Jun 2023 00:00:50 GMT

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?

Re: JSL: Select Rows Based On Timestamp

Mark_Bailey — Wed, 26 Apr 2023 14:46:04 GMT

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?

Re: JSL: Select Rows Based On Timestamp

txnelson — Wed, 26 Apr 2023 14:53:11 GMT

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

Re: JSL: Select Rows Based On Timestamp

Jeff_Perkinson — Wed, 26 Apr 2023 15:15:18 GMT

Check out .

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).

Re: JSL: Select Rows Based On Timestamp

Jeff_Perkinson — Wed, 26 Apr 2023 15:19:26 GMT

It's easier than that. You can use a time constant.

dt<<select where(:time == 01:13:32);

Re: JSL: Select Rows Based On Timestamp

haleyhoewt — Thu, 27 Apr 2023 18:45:43 GMT

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.

Re: JSL: Select Rows Based On Timestamp

txnelson — Thu, 27 Apr 2023 19:28:00 GMT

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