topic Re: Calculate time interval between 2 timestamps records in DQL

Calculate time interval between 2 timestamps records

AlexisGND — Thu, 25 Jan 2024 10:22:16 GMT

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" ?

Re: Calculate time interval between 2 timestamps records

gbaudart — Thu, 25 Jan 2024 10:26:45 GMT

Hi @AlexisGND ,

you may try this :

| fieldsAdd timeDiff = endtime - startime | fields timeDiff

Re: Calculate time interval between 2 timestamps records

AlexisGND — Thu, 25 Jan 2024 10:50:55 GMT

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

Re: Calculate time interval between 2 timestamps records

gbaudart — Thu, 25 Jan 2024 12:12:11 GMT

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.

Re: Calculate time interval between 2 timestamps records

gbaudart — Thu, 25 Jan 2024 12:21:29 GMT

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

Re: Calculate time interval between 2 timestamps records

AlexisGND — Thu, 25 Jan 2024 12:51:34 GMT

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

Re: Calculate time interval between 2 timestamps records

gbaudart — Thu, 25 Jan 2024 13:11:11 GMT

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

Re: Calculate time interval between 2 timestamps records

AlexisGND — Thu, 25 Jan 2024 13:30:19 GMT

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

Re: Calculate time interval between 2 timestamps records

gbaudart — Thu, 25 Jan 2024 13:52:40 GMT

May you paste your entire query pls ?

Re: Calculate time interval between 2 timestamps records

AlexisGND — Thu, 25 Jan 2024 14:20:23 GMT

// 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

Re: Calculate time interval between 2 timestamps records

AlexisGND — Thu, 25 Jan 2024 14:48:10 GMT

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

Re: Calculate time interval between 2 timestamps records

gbaudart — Thu, 25 Jan 2024 15:10:13 GMT

I will try to test with your dataset

Re: Calculate time interval between 2 timestamps records

krzysztof_hoja — Thu, 25 Jan 2024 15:12:08 GMT

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

Re: Calculate time interval between 2 timestamps records

AlexisGND — Thu, 25 Jan 2024 15:51:13 GMT

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 !

Re: Calculate time interval between 2 timestamps records

gbaudart — Thu, 25 Jan 2024 15:45:58 GMT

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?