convert a date field that has different date formats to epoch

[navyakem]

Hello All,

I Have a field "Due Date" that has dates in different formats like "08-09-2017 12:00" or "8/17/2017 12:59 PM".I have to find the difference between the current date and "Due date".For this i need to convert "Due Date" field to epoch(which iam unable to do).

When i try 'strptime' command,it doesnt show any values.When i use 'strftime' command like below,although the current date is getting converted,the field 'Due Date' doesnt get converted at all.

eval timenow=now() | eval ns=strftime(timenow,"%m-%d-%Y %H:%M %p") | eval ds=strftime('Due Date',"%m-%d-%Y %H:%M %p") | eval diff='Due Date' - ns.

Please suggest how can i convert "Due Date" field that has dates in different formats to epoch and find the difference?

Thanks,
Navya

[niketn · Accepted solution]

@navyakem, between your question and the example you have changed from strftime() to strptime(). Hope you understand the difference that strptime() converts string time to epoch time which can be used in time calculations. strftime() on the other hand converts epoch time to string time to make it human readable.

You would need to convert both type of time to similar format so that strptime() works on both of them. Following is run anywhere search with two dates as per your question.

PS: Pipes till table are used to just generate mock data. You would need pipe with eval afterward.

| makeresults
| eval "Due Date"="08-09-2017 12:00"
| append [| makeresults
| eval "Due Date"="8/17/2017 12:59 PM"]
| table "Due Date"
| eval Due_Date=if(match('Due Date',"/"),strptime('Due Date',"%m/%d/%Y %H:%M"),strptime('Due Date',"%m-%d-%Y %H:%M"))
| eval durationInSec=now()-Due_Date
| fieldformat Due_Date=strftime(Due_Date,"%m/%d/%Y %H:%M:%S %p")

I have used fieldformat in the end to convert Due_Date to human readable format using strftime(), while retaining underlying value as epoch. Please try out and also read about strftime(), strptime(), eval and fieldformat difference to understand these better.

[navyakem]

Thanks.Works perfect:)

[niketn]

Anytime Navya, glad it worked 🙂

[navyakem]

I just used the below query,
eval timenow=now()
| eval ds=strptime('Due Date',"%m-%d-%Y %H:%M") | eval diff=ds - timenow | table "Assigned Date","Due Date",timenow,ds,diff

Its working for Due Date where format is "08-01-2017 15:40" and doesnt work for format "8/17/2017 12:59 PM"
Assigned Date Due Date timenow ds diff
08-01-2017 15:40 08-08-2017 15:40 1503243647 1502187000.000000 -1056647.000000
08-01-2017 17:57 08-08-2017 17:57 1503243647 1502195220.000000 -1048427.000000
08-02-2017 12:00 08-09-2017 12:00 1503243647 1502260200.000000 -983447.000000
08-02-2017 15:52 08-09-2017 17:16 1503243647 1502279160.000000 -964487.000000
08-02-2017 17:47 8/17/2017 12:59 PM 1503243647

[richgalloway]

Are both date formats from the same source? If so, do you have any control over how the dates are written?

[navyakem]

They are from the same source but from a third party,so dont have any control over data