Calculate time difference between two records

[Sona282]

Hello,

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

example,

Thank you.

[JeanBlanc]

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 🙂

[krzysztof_hoja · Accepted solution]

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-functi...