topic Re: Time difference DQL in DQL

Time difference DQL

RAKESHNREDDY — Mon, 17 Mar 2025 10:31:32 GMT

Hello Everyone,

Good day,

I have a requirement to calculate the time difference between the minimum and first maximum points for all the available data within the timeframe selected. In this example, the time taken for the value to reach 98 from 0. I'm able to get the latest output using the below DQL. Is there any trick to getting the output of the complete timeframe we selected?

DQL Used

timeseries count = max(appl.completed), timestamp=start()
| fieldsAdd d=record(count=count[], timestamp=timestamp[])
| fieldsAdd d = arrayRemoveNulls( iCollectArray( if(d[][count]>97, d[] )))
| expand d
| fieldsAdd e=record(count1=count[], timestamp1=timestamp[])
| fieldsAdd e = arrayRemoveNulls( iCollectArray( if(e[][count1]<1 AND e[][timestamp1] < d[timestamp], e[] )))
| expand e
| summarize d=takeFirst(d), e=takeLast(e)
| fieldsAdd m = d[timestamp], n = e[timestamp1]
| fieldsAdd A = m - n
| fields A

Re: Time difference DQL

mark_bley — Wed, 19 Mar 2025 08:25:36 GMT

Here is an example calculating duration, hope it helps:

data record(content="79895474545006\tPreProcessing\tstart\t1\t\t2023-04-27 04:23:12.000.153"), record(content="79895474545006\tPreProcessing\tend\t1\t\t2023-04-27 04:30:38.000.153"), record(content="79895474545005\tPreProcessing\tstart\t1\t\t2023-04-26 05:30:38.000.1538"), record(content="79895474545005\tPreProcessing\tend\t1\t\t2023-04-27 04:30:38.000.153") | parse content, """ALNUM:batch.id '\t' STRING:stage '\t' LDATA:stage_status '\t' INT?:valid '\t' FLOAT?:result PUNCT? '\t' TIMESTAMP('yyyy-MM-DD HH:mm:ss'):timestamp""" | filter stage == "PreProcessing" | summarize {start=takeFirst(if(stage_status=="start",timestamp)), end=takeFirst(if(stage_status=="end",timestamp))}, by:{batch.id, stage} | fieldsAdd duration = end - start

Re: Time difference DQL

RAKESHNREDDY — Wed, 19 Mar 2025 09:54:59 GMT

Thanks Mark, tried including the 'By: Day' with a few modifications in the summarize. Works fine now.

Re: Time difference DQL

marco_irmer — Wed, 19 Mar 2025 23:52:02 GMT

Perhaps something like this would work for you:

timeseries count = max(appl.completed) | fieldsAdd minVal = arrayMin(count), maxVal = arrayMax(count) | fieldsAdd minIndex = arrayLastIndexOf(count, minVal), maxIndex = (arraySize(count) -1) - arrayLastIndexOf(arrayReverse(count), maxVal) | fieldsAdd timeDistance =(maxIndex - minIndex) * interval