cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Calculate time interval between 2 timestamps records

AlexisGND
Participant

AlexisGND_0-1706177661722.png

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

14 REPLIES 14

gbaudart
Champion

Hi @AlexisGND ,

you may try this :

 

| fieldsAdd timeDiff = endtime - startime
| fields timeDiff

 

 

 

Observability consultant - Dynatrace Associate Certified

Hi @gbaudart, thanks for your reply.

I tried this DQL command :

AlexisGND_2-1706179239363.png

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 :

AlexisGND_1-1706179220337.png

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 :

AlexisGND_4-1706179692560.png

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

AlexisGND_3-1706179656623.png

 

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.

Observability consultant - Dynatrace Associate Certified

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
Observability consultant - Dynatrace Associate Certified

AlexisGND_0-1706186151819.png

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

AlexisGND_1-1706186193419.png

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

AlexisGND_2-1706187082909.png

 


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

I will let you know if i find anything

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


 

Observability consultant - Dynatrace Associate Certified

AlexisGND_0-1706189110413.png

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

May you paste your entire query pls ? 

Observability consultant - Dynatrace Associate Certified

AlexisGND_0-1706192175103.png

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

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

AlexisGND_0-1706194067044.png

 

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

 

I will try to test with your dataset

Observability consultant - Dynatrace Associate Certified

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?

Observability consultant - Dynatrace Associate Certified

krzysztof_hoja
Dynatrace Advisor
Dynatrace Advisor

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

 

krzysztof_hoja_0-1706195439880.png

 

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 :

AlexisGND_0-1706197019925.png

Thanks @gbaudart and @krzysztof_hoja for your help !

Featured Posts