Search for closets timestamp

[Fro2]

I have a date and time (string) column in an array along with some other data.  I need to perform an operation on data from a particular start and stop time; however the time I need to find may not exactly be in the array.  So I need a way to extract the associated index in the array for the closest date/time combination.   It may be off as much as 4 seconds.  The following is the approach I am about to start writing code for to see if I can get it to work.  Do you have any other suggestion and/or implementation to do this?

1. Extract the columns date and time from the array. 
2. Combine the date and time into one string in the format “mm/dd/yy hh:mm:ss” 
3. Convert this date/time into a Timestamp, now I will have an array of Timestamps
4. Prompt for the start time and the stop time
5. Use the “Search 1D Array” on the array of Timestamps for the start time, I know it may not be found due to the +/- 4 second problem.  If found  use the index of element.  However if the index of element it -1 this means the timestamp was not found; therefore must find the closest value.

Well this is where I am stuck in my brainstorm.  I was thinking in a loop keep adding 1 second to the start time and perform the search until a match is found.  Use the index of the match and extract the timestamp.  Subtract one from the index and extract that timestamp.  Subtract both of the extracted timestamps from the original Start time and use the index which has the smallest difference.  These sounds  like it may work but I assume there is an easier approach. 

Hey I was just thinking maybe I can use the “In Range and Coerce” function!

I will try to program now this as written above.

[falkpl]

Once you have an array of timestamps, subtract the start time from the array (very cool that the difference between 2 labview timestamps is a doublke value in seconds), then take the absolute value and find min value/index using the array min max function. Repete this for the stop time. the 2 results are the closst index times form the original array.

[Fro2 · Accepted solution]

Thanks falkpl for your suggestion.  I was able to implement my brainstormed method except I decide to just use the next record instead of looking at the record before and after.  I incorporated both your method (closest record) and my method (next record) together in one vi and named it DateTimeMatch.  The vi will accept and array of timestamps and a match time and it outputs the closest/next match (dependent on the Boolean input, default is false for closest record) as well as the index for the match and the number of seconds offset.  See below:

The hardest thing, which I thought was going to be the simplest, was converting the first two columns in the CSV file into an array of timestamps.  The way I did it is shown below.  Note in the image of how I incorporated the two Vis together.

Thanks again.

[blawson]

take a look at the "Scan from string" function in the strings palette, and use it in conjunction with the "Format Codes for the Time Format String" help file, under "time, format codes"

[Fro2]

Blawson,

I may have done it wrong but I did look into using a variety of different string functions to format the string into a pattern to be acceptable and converted to a TimeStamp.  The only function that I could find that definetly would convert directly to a timestamp, was the "seconds to timestamp" function.  But to use it one would have to do some math on the string to convert the date and then the time to a total number for seconds since January 01, 1904.  It seemed easier to just use the bundle by name and convert to timestamp.

To clarify the first column in the CSV has the date in the format mm/dd/yyyy and the second column in the CSV has the time in the format hh:mm:ss.  I converted these two coulmns into one string and based on the month, day, year, hour, etc. sent the value to the bundle by name function and finally converted that bundle to a timestamp.  This method worked I was just suprised I had to do it that way.

I assume you were refering to the "Scan From String" function which scans the input string and converts the string according to format codes. Not sure how to use it to get what I needed.

Fro2

[Fro2]

Blawson,

Oh even though I wrote “scan from string” I was thinking in my mind “Scan String From Token” that was what I could not figure out how to use.  Were you implying to use the “Scan from From String” in lieu of the “Search/Split String”?  If so, wouldn’t one still would need the “Decimal String to Number” function.

Fro2

[GerdW]

Hi Fro2,

yes, we are talking about ScanFromString:

Edit: Just read your full answer on date format, so change the format string to incorporate "%m/%d/%y"...

P.S.:

Not sure what you're talking about, but my favorite dictionary translates "closet" to either "toilet" or "built-in wardrobe"

[Fro2]

GerdW,

Oh I see this way you do not even need the "Decimal String to Number", "Bundle By Name" nor the "Date/Time to Seconds".  the one function "Scan from String" replaces all three.  I added a "Concatenate Strings" to create one string and then used your suggestion.

THANKS!!!!

Fro2