topic Re: How to calculate difference between two time stamps in Getting Data In

How to calculate difference between two time stamps

ken_lynch — Wed, 19 Aug 2020 14:31:11 GMT

Hi, I have seen a few post on this subject, but none seem to fix my issue. I am trying to calculate the difference between two date/time stamps.

| eval CompleteDate=if(isnull(CompleteDate) OR len(CompleteDate)==0,strftime(now(),"%Y-%m-%d %H:%M:%S:%7Q"),CompleteeDate)
|eval Start = strptime(AwaitingResponseDate,"%Y-%m-%d %H:%M:%S:%7Q")
|eval End = strptime(CompleteDate,"%Y-%m-%d %H:%M:%S:%7Q")
|eval WaitTime = Start-End

The issue seems to be that the Start field is empty when i add it to a table, however, the End time works. The only difference between start and end is that end is being set by the eval/if statement for CompleteDate because all are null. Start/AwaitingResponseDate is an auto extracted field

The date/time format is the same for each filed. This is an example of the AwaitingResponseDate value
2020-07-20 18:35:15.0000000
This is an example of the inserted CompleteDate field from the same record

2020-08-19 09:19:53:0000000

Any help is certainly appreciated.

Re: How to calculate difference between two time stamps

harsmarvania57 — Wed, 19 Aug 2020 14:45:03 GMT

In your AwaitingResponseDate after second it is . (dot) not : (colon) 2020-07-20 18:35:15.0000000

So your query will be

| eval CompleteDate=if(isnull(CompleteDate) OR len(CompleteDate)==0,strftime(now(),"%Y-%m-%d %H:%M:%S:%7Q"),CompleteeDate) |eval Start = strptime(AwaitingResponseDate,"%Y-%m-%d %H:%M:%S.%7Q") |eval End = strptime(CompleteDate,"%Y-%m-%d %H:%M:%S:%7Q") |eval WaitTime = Start-End

Re: How to calculate difference between two time stamps

ken_lynch — Wed, 19 Aug 2020 14:57:51 GMT

Thank you so much....it is the little things 🙂
Also helps if I use End-Start instead Start-End....