topic Calculate time difference between two records in DQL

Calculate time difference between two records

Sona282 — Wed, 25 Sep 2024 06:29:15 GMT

Hello,

I want to calculate the time difference the current record and previous record, until the end of the record.

example,

Thank you.

Re: Calculate time difference between two records

JeanBlanc — Tue, 24 Sep 2024 15:34:18 GMT

Hi.

The easiest way to do this is to convert your DateTime values and then subtract the start time from the end time.

| fieldsAdd myendtime = toTimestamp(myendtime), mystarttime = toTimestamp(mystarttime)
| fieldsAdd myduration = myendtime - mystarttime

You might need to perform the same subtraction multiple times until the end of the record.

Let me know if this works for you 🙂

Re: Calculate time difference between two records

krzysztof_hoja — Wed, 25 Sep 2024 21:25:50 GMT

Window function are not available yet, but it is possible under certain limitations using collectArray , array sorting, and arrayDelta functions and your data set has well defined order:

data record(timestamp=now(), tx="a"), record(timestamp=now()+500ms, tx="b"), record(timestamp=now()+2s, tx="c"), record(timestamp=now()+3s, tx="d"), record(timestamp=now()+4s, tx="e"), record(timestamp=now()+5s, tx="f"), record(timestamp=now()+13s, tx="g"), record(timestamp=now()+14s, tx="h"), record(timestamp=now()+15s, tx="i"), record(timestamp=now()+16s, tx="j") | sort timestamp asc | fieldsAdd d = record(t=timestamp, tx) | summarize d = collectArray(d) | fieldsAdd d =arraySort(d, direction:"ascending") | fieldsAdd dt = arrayDelta(iCollectArray(unixNanosFromTimestamp(d[][t]))) | fields d = record( timestamp=d[][t], dt = duration(dt[], unit:"ns"), tx=d[][tx]) | expand d | fields timestamp=d[timestamp], dt=d[dt], tx=d[tx] | sort timestamp asc

Result looks like this:

max number of records processed using this method is 131072 according to documentation: https://docs.dynatrace.com/docs/platform/grail/dynatrace-query-language/functions/aggregation-functions#collectArray