How can I get my inputlookup to ignore the time within the timestamp?

[tkwaller_2]

Here's What I have to fix but haven't yet figred out how.
In this search

 index=dev_tsv "BO Type"="assessments"  
  |dedup "Workflow Name" "Workflow Phase" "Workflow Process Due Date" "Workflow Process Name" "Workflow Process Sort Order" "Workflow Started Date" "Workflow Step Name" "Step Due Date" "Workflow Step Sort Order"
  | table "Workflow Name" "Workflow Phase" "Workflow Process Due Date" "Workflow Process Name" "Workflow Process Sort Order" "Workflow Started Date" "Workflow Step Name" "Step Due Date"  "Workflow Step Sort Order" 
  | convert timeformat="%Y-%m-%d %H:%M:%S.%6N"  mktime("Step Due Date") AS cumulativeDueDate 
  | eval dueDateRange=mvrange(cumulativeDueDate,now(),86400)
  | mvexpand dueDateRange
  | convert ctime(dueDateRange) timeformat="%+" 
  | where  NOT match(dueDateRange,"(Sun|Sat).*") AND  NOT [ | inputlookup exclude_holidays  | eval holiday_date=strptime(holiday_date, "%Y-%m-%d %H:%M:%S") | rename holiday_date as dueDateRange | eval dueDateRange=strftime(dueDateRange, "%+") ]

The initial value of dueDateRange here is formatted like
"%Y-%m-%d %H:%M:%S.%6N"
after input lookup it is
"%Y-%m-%d %H:%M:%S"
but the "%H:%M:%S" portion is always
"00:00:00" in the input lookup values, so I don't think the inputlookup portion is working to exclude as the fields in the actual data have valid "%H:%M:%S"
for example:
"2018-07-04 07:51:25.966000"

this is the value of input lookup file:

holiday_date
 2018-01-01 00:00:00
 2018-01-15 00:00:00
 2018-02-19 00:00:00
 2018-05-28 00:00:00
 2018-07-04 00:00:00
 2018-09-03 00:00:00
 2018-10-08 00:00:00

Any thoughts on how I can get this to work by not using the time inside the inputlookup value timestamp? So it would use %Y-%m-%d?

[DalJeanis · Accepted solution]

instead of

    | eval holiday_date=strptime(holiday_date, "%Y-%m-%d %H:%M:%S")

use

    | eval holiday_date=relative_time(strptime(holiday_date, "%Y-%m-%d %H:%M:%S"),"@d")

[tkwaller_2]

Hello @DalJeanis my updated search like:

index=dev_tsv "BO Type"="assessments"  
|dedup "Workflow Name" "Workflow Phase" "Workflow Process Due Date" "Workflow Process Name" "Workflow Process Sort Order" "Workflow Started Date" "Workflow Step Name" "Step Due Date" "Workflow Step Sort Order"
 | table "Workflow Name" "Workflow Phase" "Workflow Process Due Date" "Workflow Process Name" "Workflow Process Sort Order" "Workflow Started Date" "Workflow Step Name" "Step Due Date"  "Workflow Step Sort Order" 
 | convert timeformat="%Y-%m-%d %H:%M:%S.%6N"  mktime("Step Due Date") AS cumulativeDueDate 
 | eval dueDateRange=mvrange(cumulativeDueDate,now(),86400)
 | mvexpand dueDateRange
 | convert ctime(dueDateRange) timeformat="%+" 
 | where  NOT match(dueDateRange,"(Sun|Sat).*") AND  NOT [ | inputlookup exclude_holidays  | eval holiday_date=relative_time(strptime(holiday_date, "%Y-%m-%d %H:%M:%S"),"@d") | rename holiday_date as dueDateRange | eval dueDateRange=strftime(dueDateRange, "%+") ]

but it still returns the same results, I still get records with dueDateRange = Wed Jul 4 07:35:35 GMT 2018 when it should be excluded since holiday_date = Wed Jul 4 00:00:00 GMT 2018

I just cant get it to ignore the time within the timestamp

[DalJeanis]

You need to do the relative_time(datefield,"@d") on both sides of the lookup.

Also, there's a basic issue you might run into with "it's the Fourth of July holiday WHERE"?

The records are stored in UTC, (which we'll pretend for the moment is London's time zone.) If a user is viewing the search in, say, San Francisco, then the interface may choose to determine what date/time the record occurred in San Francisco, or might not, depending on how the search and the system are set up.

So, if converting the other dat field doesn't do the trick, then before you do any more the conversion and testing in the above search, you need to determine precisely how the data is stored and precisely how the system is interpreting relative_time( sometimefield,"@d").