Filtering data from lookup csv file based on time difference

[sambit_kabi]

I have a lookup csv file which has the following data.
Day Messages
12/02/2020 1571
12/02/2020 302
12/02/2020 1

What I want to do is read the Day column and then subtract the day from today's date to check if the difference is greater than 30.
If the diff > 30 filter it out.

I tried the following query and it doesn't work.

| inputlookup messages_per_day.csv | eval today=strftime(now(), "%d/%m/%Y") | eval diff=today - Day

[sambit_kabi · Accepted solution]

I managed to solve this in the following manner. Though it may not be efficient but it worked for me.

Instead of the date I kept the time as "seconds from epoch" in the csv file which looked like(assume the data is dummy).

Day Messages
12022020 1571
12022020 302
12022020 1

Then using the following construct i was able to take a diff and compare it.
| inputlookup abc.csv | eval today=relative_time(now(), "%d") | eval diff = today - Day | where diff <= 86400

I am sure the strptime function should have done the job too with my previous approach but somehow in my case it didn't convert the date in the csv to date that can be compared. So I took this approach. Still trying to figure out why strptime didn't work.

[spayneort]

| inputlookup messages_per_day.csv 
| where relative_time(now(),"-30d@d") < strptime(Day, "%d/%m/%Y")

[sambit_kabi]

Thanks for the help. I used the relative_time function as you pointed out. That came in handy.
Unfortunately the strptime function didn't work for me as I have mentioned before. It didn't convert the time in csv to time to be compared. I don't get result for today variable.

However I managed to find a solution by dumping the time in "secs from epoch" in the csv and then compared the diff between the today's start time with the time in csv.

used constructs like | eval diff=relative_time(now(), "@d") - Day | where diff < 86400.

Still wondering why strptime didn't work for me. However I found that | convert ctime(Day) did work for me

Is there any condition that strptime has when used with inputlookup. Nothing mentioned in the documentation.

[richgalloway]

Timestamps cannot be compared in string form. Try the inverse of your query.

| inputlookup messages_per_day.csv | eval today=strptime(Day, "%d/%m/%Y") | eval diff=now() - today

[sambit_kabi]

Thanks for the help. Unfortunately the strptime function didn't work for me. It didn't convert the time in csv to time to be compared. I don't get result for today variable.

However I managed to find a solution by dumping the time in "secs from epoch" in the csv and then compared the diff between the today's start time with the time in csv.

used constructs like | eval diff=relative_time(now(), "@d") - Day | where diff < 86400.

Still wondering why strptime didn't work for me. However I found that | convert ctime(Day) did work for me.