In the following query, what is wrong with this date difference?

[collinlorb]

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

[somesoni2]

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

[morethanyell]

| 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")

[collinlorb]

That code actually returns nothing for StartDate or EndDate

[collinlorb]

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

[morethanyell]

| 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")