Calculate time interval between 2 timestamps records

[AlexisGND]

Hi, I ingested customized logs into Dynatrace.

I have 2 columns named "bmx.hip.execution.endtime" and "bmx.hip.execution.starttime" with are timestamps in iso format.

Is there any way to calculate the time interval between the two of them in a separate column that I created "timediff" ?

[gbaudart]

Hi @AlexisGND ,

you may try this :

| fieldsAdd timeDiff = endtime - startime
| fields timeDiff

[AlexisGND]

Hi @gbaudart, thanks for your reply.

I tried this DQL command :

I just changed the name of the column in english (and not in french).

But "timeDiff" is still on "null", does that means that timeDiff is not able to calculate time interval with 2 iso formats ?

Result :

If it's not possible to calculate the time interval, i tried an alternative way (only if the DQL above is not the solution) :

(i tried an alernative way to do substings with the timestamp in order to get and associates numbers with Year, month etc)
It's done like this :

But i get an error that shows it's not possible to calculate between numbers (or i don't understand the error message).

[gbaudart]

I'm doing exactly the same thing in my environment with the DQL request I sent.
There must be something to fix.
I'll think about it.

[gbaudart]

Try this :

| fieldsAdd StartTimeModif = formatTimestamp(StartTime,format:"dd MMM YYYY HH:mm:ss"), EndTimeModif = formatTimestamp(EndTime,format:"dd MMM YYYY HH:mm:ss"), DiffTime = EndTimeModif - StartTimeModif
| fields DiffTime

[AlexisGND]

DiffTime is still on "null"
Even if i change to End_execution and Start_execution

Even when i display everything, I have null on StartTime and EndTime :

I am currently looking at the time functions documentation Time functions - Dynatrace Docs

I will let you know if i find anything

[gbaudart]

your variable aren't good.

Try this :

| fieldsAdd StartTimeModif = formatTimestamp(Start_execution,format:"dd MMM YYYY HH:mm:ss"), EndTimeModif = formatTimestamp(End_execution,format:"dd MMM YYYY HH:mm:ss"), DiffTime = EndTimeModif - StartTimeModif
| fields DiffTime

[AlexisGND]

I changed the time format because I saw in the line : 

format:"dd MMM YYYY HH:mm:ss"

 Now Start and End have time but DiffTime has nothing

[gbaudart]

May you paste your entire query pls ? 

[AlexisGND]

// get logs
fetch logs
// filter timestamp descending
| sort timestamp desc

// rename columns
| fieldsRename End_execution = bmx.hip.execution.endtime
| fieldsRename Start_execution = bmx.hip.execution.starttime
| fieldsRename HostName = bmx.hip.host.name

// filter host names with Datastage
| filter HostName == "Datastage" or HostName == "DATASTAGE" or HostName == "datastage"

// display columns
| fieldsAdd StartTimeModif = formatTimestamp(Start_execution,format:"dd MMM YYYY HH:mm:ss"), EndTimeModif = formatTimestamp(End_execution,format:"dd MMM YYYY HH:mm:ss"), DiffTime = EndTimeModif - StartTimeModif
| fields DiffTime, Start_execution, StartTimeModif, End_execution, EndTimeModif

the "formatted" field was just a test.
But my guess is the incorrect format of Start_execution and End_execution

[AlexisGND]

By doing a test, when I do "formatTimestamp", the default timestamp is correct but even when the Endtime is with the same format as the timestamp, still got a null.
I really don't get it... 😞

fetch logs
| sort timestamp desc
| filter bmx.hip.host.name == "Datastage"

| fieldsAdd formatted = formatTimestamp(timestamp, format:"MM-dd-YYYY")
| fieldsAdd formatted2 = formatTimestamp(bmx.hip.execution.endtime, format:"MM-dd-YYYY")

| fields timestamp, formatted, bmx.hip.execution.endtime, formatted2

[gbaudart]

I will try to test with your dataset

[gbaudart]

If you just try this :

fetch logs
| sort timestamp desc
| filter bmx.hip.host.name == "Datastage"

| fields toTimestamp(bmx.hip.execution.endtime), toTimestamp(bmx.hip.execution.starttime)

Do you also get "null" for the 2 results?

[krzysztof_hoja · Accepted solution]

I suspect that ingested fields are just Strings. To do any arithmetics they need to be converted to timestamps. toTimestamp function should catch iso format. Please try query based on this example:

data record(ts="2024-01-22 15:30:01.000000", te="2024-01-22 15:30:01.500000")
| fieldsAdd pts=toTimestamp(ts), pte=toTimestamp(te)
| fieldsAdd d=pte-pts

[AlexisGND · Accepted solution]

So I finally arrived at the end result 🙂

What I did ?
The start time (as bmx.hip.execution.starttime) and end time (as bmx.hip.execution.endtime) were converted to timestamp formats with this lines :

// convert start and end times to timestamp format
| fieldsAdd bmx.hip.execution.endtime = toTimestamp(bmx.hip.execution.endtime),
bmx.hip.execution.starttime = toTimestamp(bmx.hip.execution.starttime)

And then I added a new field to substract the endtime with the starttime.

| fieldsAdd bmx.hip.execution.duration = bmx.hip.execution.endtime - bmx.hip.execution.starttime

Final dql :

// get logs
fetch logs
// filter timestamp descending
| sort timestamp desc

// filter host names with Datastage
| filter bmx.hip.host.name == "Datastage" or bmx.hip.host.name == "DATASTAGE" or bmx.hip.host.name == "datastage"
| filter loglevel == "NOTICE" or loglevel == "NONE" or loglevel == "DEBUG"

// convert start and end times to timestamp format
| fieldsAdd bmx.hip.execution.endtime = toTimestamp(bmx.hip.execution.endtime),
bmx.hip.execution.starttime = toTimestamp(bmx.hip.execution.starttime)
| fieldsAdd bmx.hip.execution.duration = bmx.hip.execution.endtime - bmx.hip.execution.starttime

// display columns
| fields content, bmx.hip.execution.starttime, bmx.hip.execution.endtime, bmx.hip.execution.duration, bmx.hip.execution.status

// rename columns
| fieldsRename End_execution = bmx.hip.execution.endtime
| fieldsRename Start_execution = bmx.hip.execution.starttime

 
Final visual result :

Thanks @gbaudart and @krzysztof_hoja for your help !