Difference between the 2 time fields

[Real_captain]

Hi 

Can someone please let me know how i can find the difference between the 2 fields Start-Time and End-Time in the below search. 

Format of time extracted by the query is : 

Start-Time = 2024-01-23T11:38:59.0000000Z

End-Time = 2024-01-23T11:39:03.0000000Z

Query : 

`macro_events_prod_srt_shareholders_esa` eocEnv = PRO * "MICROSOFT.DATAFACTORY" activityName = Merge_Disclosure_Request 741b5db8-da47-468b-b883-a06ef137519a
| eval Dreqid=case('category'="PipelineRuns",'properties.Parameters.DisclosureRequestId','category'="ActivityRuns",'properties.Input.storedProcedureParameters.DisclosureRequestId.value',1=1,"")
| eval end_time=case('end'="1601-01-01T00:00:00.0000000Z", "Still-Running",1=1,'end')
| table eocEnv , start , end_time , pipelineName , activityName, pipelineRunId,level , status , category , Type , Dreqid, properties.Error.errorCode , properties.Error.message
| rename Dreqid as "Disclosure request id" , eocEnv as "Environment" , EOC_ResourceGroup as " Resource_Group" , activityName as "Activity Name" , pipelineName as "Pipeline Name" , operationName as "Operation Name" , pipelineRunId as "Run_Id" , level as "Level" , status as "STATUS" , category as "Category" , start as "Start-Time" , end_time as "End-Time" , properties.Error.errorCode as "Error-Code" , properties.Error.message as "Error-Message"
| sort -"Start-Time"

Real_captain_0-1706019257853.png

[richgalloway · Accepted solution]

Find the difference between two timestamps by converting each into epoch (integer) format using the strptime function and then subtract them.

| eval eStartTime=strptime('Start-Time', "%Y-%m-%dT%H:%M:%S.%6N%Z")
| eval eEndTime=strptime('End-Time', "%Y-%m-%dT%H:%M:%S.%6N%Z")

P.S.  Avoid using hyphens in field names as they can be mis-interpreted as the subtraction operator.