Below is my code: I have followed suggestions found on here for date & time difference, yet this one is not working. What's wrong with it?
| from datamodel:"TestTable"
| where scope LIKE "Test%"
| where us_schedule_state like "Accepted"
| where !LIKE(iteration, "%G")
| eval EndDate = strptime(AcceptedDate, "%Y-%m-%d")
| eval StartDate = strptime(trans_to_inprogress_date, "%Y-%m-%d")
| eval DateDiff = tostring((EndDate - StartDate), "duration")
| eval Date = strftime(1533096000.000000, "%Y-%m-%d %H:%S")
| where strftime(StartDate, "%Y-%m-%d %H:%S") > Date
Both operands in your last where clause are string. To compare dates, use the epoch formatted value.
| from datamodel:"TestTable"
| where scope LIKE "Test%"
| where us_schedule_state like "Accepted"
| where !LIKE(iteration, "%G")
| eval EndDate = strptime(AcceptedDate, "%Y-%m-%d")
| eval StartDate = strptime(trans_to_inprogress_date, "%Y-%m-%d")
| eval DateDiff = tostring((EndDate - StartDate), "duration")
| eval Date = strftime(1533096000.000000, "%Y-%m-%d %H:%S")
| where StartDate > strptime(Date, "%Y-%m-%d %H:%S")
OR simply
| from datamodel:"TestTable"
| where scope LIKE "Test%"
| where us_schedule_state like "Accepted"
| where !LIKE(iteration, "%G")
| eval EndDate = strptime(AcceptedDate, "%Y-%m-%d")
| eval StartDate = strptime(trans_to_inprogress_date, "%Y-%m-%d")
| eval DateDiff = tostring((EndDate - StartDate), "duration")
| eval Date = strftime(1533096000.000000, "%Y-%m-%d %H:%S")
| where StartDate > 1533096000.000000
| where strftime(StartDate, "%Y-%m-%d %H:%S") > Date
the code above parses Hour : Second which doesn't make sense
also, try this
| eval EndDate = strptime(AcceptedDate . " 00:00:00", "%Y-%m-%d %H:%M:%S")
| eval StartDate = strptime(trans_to_inprogress_date . " 00:00:00", "%Y-%m-%d %H:%M:%S")
EDIT
if above doesn't work because you mentioned the time format looks like "2018-11-16T14:10:20.969Z", try this:
| rex field=AcceptedDate "(?<date_ymd_ad>[^/]*)T(?<date_hms_ad>[^//]*)."
| rex field=trans_to_inprogress_date "(?<date_ymd_ttid>[^/]*)T(?<date_hms_ttid>[^//]*)."
| eval EndDate = strptime(date_ymd_ad . " " . date_hms_ad, "%F %X.%3N")
| eval StartDate = strptime(date_ymd_ttid . " " . date_hms_ttid, "%F %X.%3N")
| eval DateDiff = tostring(round(EndDate - StartDate, 3), "duration")
That code actually returns nothing for StartDate or EndDate
Could this be because of the format of the field that I am using strptime for in the first place? Here is what it looks like.
2018-11-16T14:56:20.969Z
| rex field=AcceptedDate "(?<date_ymd_ad>[^/]*)T(?<date_hms_ad>[^//]*)."
| rex field=trans_to_inprogress_date "(?<date_ymd_ttid>[^/]*)T(?<date_hms_ttid>[^//]*)."
| eval EndDate = strptime(date_ymd_ad . " " . date_hms_ad, "%F %X.%3N")
| eval StartDate = strptime(date_ymd_ttid . " " . date_hms_ttid, "%F %X.%3N")
| eval DateDiff = tostring(round(EndDate - StartDate, 3), "duration")