Getting Data In

How To Get Datetime Difference?

vtsguerrero
Contributor

Hello guys, can anyone help me to get the lenght of each operation?
I have start datetime and end datetime, both are in brazil timestamp Day/Month/Yeah-Hour:Minutes:Seconds.
How can I do that?

Follows a piece of sample:

index=full sourcetype=temp DATA_INICIO=* DATA_FIM=* 
| eval TEMPO_EXECUCAO = (DATA_FIM - DATA_INICIO)
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO

DATA_INICIO                   DATA_FIM                              TEMPO_EXECUCAO
26/02/2015-04:07:06             26/02/2015-05:01:43  
0 Karma
1 Solution

emiller42
Motivator

I bet the problem is that your fields are being treated as strings and not timestamps. You can correct this with eval

index=full sourcetype=temp DATA_INICIO=* DATA_FIM=*
| eval DATA_INICIO=strptime( DATA_INICIO, "%d/%m/%Y-%T") 
| eval DATA_FIM=strptime( DATA_FIM, "%d/%m/%Y-%T") 
| eval TEMPO_EXECUCAO=DATA_FIM-DATA_INICIO 
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO
| fieldformat DATA_INICIO=strftime(DATA_INICIO, "%d/%m/%Y-%T")
| fieldformat DATA_FIM=strftime(DATA_FIM, "%d/%m/%Y-%T")
| fieldformat TEMPO_EXECUCAO=tostring(TEMPO_EXECUCAO, "duration")

the fieldformat lines aren't strictly necessary, but make the output more readable. (With the exception of _time, all timestamp and duration values are displayed as decimals)

Relevant links:
Functions for Eval and Where (search for strptime/strftime)
Fieldformat

I successfully tested the above by using the following:

index=_internal | head 1 | eval DATA_INICIO="26/02/2015-04:07:06" | eval DATA_FIM="26/02/2015-05:01:43" | table DATA_INICIO DATA_FIM 
| eval DATA_INICIO=strptime( DATA_INICIO, "%d/%m/%Y-%T") 
| eval DATA_FIM=strptime( DATA_FIM, "%d/%m/%Y-%T") 
| eval TEMPO_EXECUCAO=DATA_FIM-DATA_INICIO 
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO
| fieldformat DATA_INICIO=strftime(DATA_INICIO, "%d/%m/%Y-%T")
| fieldformat DATA_FIM=strftime(DATA_FIM, "%d/%m/%Y-%T")
| fieldformat TEMPO_EXECUCAO=tostring(TEMPO_EXECUCAO, "duration")

output:

DATA_INICIO         DATA_FIM               TEMPO_EXECUCAO
26/02/2015-04:07:06 26/02/2015-05:01:43 00:54:37.000000

View solution in original post

dwaddle
SplunkTrust
SplunkTrust

Convert them to time_t values using the eval command and its strptime function.

 index=full sourcetype=temp DATA_INICIO=* DATA_FIM=* 
 | eval DATA_FIM_t = strptime(DATA_FIM,"%d/%m/%Y-%H:%M:%S")
 | eval DATA_INICIO_t = strptime(DATA_INICIO,"%d/%m/%Y-%H:%M:%S")
 | eval TEMPO_EXECUCAO_t = (DATA_FIM_t - DATA_INICIO_t)
 | eval TEMPO_EXECUCAO = tostring(TEMPO_EXECUCAO_t,"duration")
 | table DATA_INICIO DATA_FIM TEMPO_EXECUCAO

emiller42
Motivator

I bet the problem is that your fields are being treated as strings and not timestamps. You can correct this with eval

index=full sourcetype=temp DATA_INICIO=* DATA_FIM=*
| eval DATA_INICIO=strptime( DATA_INICIO, "%d/%m/%Y-%T") 
| eval DATA_FIM=strptime( DATA_FIM, "%d/%m/%Y-%T") 
| eval TEMPO_EXECUCAO=DATA_FIM-DATA_INICIO 
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO
| fieldformat DATA_INICIO=strftime(DATA_INICIO, "%d/%m/%Y-%T")
| fieldformat DATA_FIM=strftime(DATA_FIM, "%d/%m/%Y-%T")
| fieldformat TEMPO_EXECUCAO=tostring(TEMPO_EXECUCAO, "duration")

the fieldformat lines aren't strictly necessary, but make the output more readable. (With the exception of _time, all timestamp and duration values are displayed as decimals)

Relevant links:
Functions for Eval and Where (search for strptime/strftime)
Fieldformat

I successfully tested the above by using the following:

index=_internal | head 1 | eval DATA_INICIO="26/02/2015-04:07:06" | eval DATA_FIM="26/02/2015-05:01:43" | table DATA_INICIO DATA_FIM 
| eval DATA_INICIO=strptime( DATA_INICIO, "%d/%m/%Y-%T") 
| eval DATA_FIM=strptime( DATA_FIM, "%d/%m/%Y-%T") 
| eval TEMPO_EXECUCAO=DATA_FIM-DATA_INICIO 
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO
| fieldformat DATA_INICIO=strftime(DATA_INICIO, "%d/%m/%Y-%T")
| fieldformat DATA_FIM=strftime(DATA_FIM, "%d/%m/%Y-%T")
| fieldformat TEMPO_EXECUCAO=tostring(TEMPO_EXECUCAO, "duration")

output:

DATA_INICIO         DATA_FIM               TEMPO_EXECUCAO
26/02/2015-04:07:06 26/02/2015-05:01:43 00:54:37.000000

vtsguerrero
Contributor

Thanks a lot @emiller42!
Worked pretty well now...

0 Karma

vtsguerrero
Contributor

Hello! Just a last question...
How would I make this shows only time difference in Hours:Minutes:Seconds ( 2 characters at most for each ) example
"Lenght = 01:32:18"

It's is currently this way:

index=full sourcetype=temp DATA_INICIO=* DATA_FIM=*  
| eval DATA_INICIO=strptime( DATA_INICIO, "%d/%m/%Y-%T")   
| eval DATA_FIM=strptime( DATA_FIM, "%d/%m/%Y-%T")   
| eval TEMPO_EXECUCAO=DATA_FIM-DATA_INICIO   
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO  
| fieldformat DATA_INICIO=strftime(DATA_INICIO, "%d/%m/%Y-%T")  
| fieldformat DATA_FIM=strftime(DATA_FIM, "%d/%m/%Y-%T")  
| fieldformat TEMPO_EXECUCAO=tostring(TEMPO_EXECUCAO, "duration")  
| fieldformat DATA_INICIO=strftime(DATA_INICIO, "%d/%m/%Y-%T")  
| fieldformat DATA_FIM=strftime(DATA_FIM, "%d/%m/%Y-%T")  
| fieldformat TEMPO_EXECUCAO=tostring(TEMPO_EXECUCAO, "duration")
| fieldformat TEMPO_EXECUCAO=strftime(TEMPO_EXECUCAO, "%d/%m/%Y-%T")
0 Karma

emiller42
Motivator

So doing a strftime on TEMPO_EXECUCAO doesn't make sense because it's not a timestamp. And why are you fieldformatting the other fields twice? That's entirely redundant.

I was able to generate the output you're looking for via sed replacement:

index=_internal | head 1 | eval DATA_INICIO="26/02/2015-04:07:06" | eval DATA_FIM="26/02/2015-05:01:43" | table DATA_INICIO DATA_FIM 
 | eval DATA_INICIO=strptime( DATA_INICIO, "%d/%m/%Y-%T") 
 | eval DATA_FIM=strptime( DATA_FIM, "%d/%m/%Y-%T") 
 | eval TEMPO_EXECUCAO=DATA_FIM-DATA_INICIO 
 | table DATA_INICIO DATA_FIM TEMPO_EXECUCAO
 | fieldformat DATA_INICIO=strftime(DATA_INICIO, "%d/%m/%Y-%T")
 | fieldformat DATA_FIM=strftime(DATA_FIM, "%d/%m/%Y-%T")
 | fieldformat TEMPO_EXECUCAO=replace(tostring(TEMPO_EXECUCAO, "duration"),"^(.*)(\d{2}:\d{2}:\d{2})(.*)","\2")
0 Karma

gfuente
Motivator

Hello

You need to get your time to epoch, then calculate the difference, and then convert it back to human format. Something like:

... your base search | eval DATA_FIM_EPOCH = strptime(DATA_INICIO,"%d/%m/%Y-%H:%M:%S") | eval DATA_FIM_EPOCH = strptime(DATA_FIM,"%d/%m/%Y-%H:%M:%S") | eval difference_epoch = DATA_FIM_EPOCH - DATA_FIM_EPOCH | eval TEMPO_EXECUCAO = tostring(difference_epoch,"duration") | table table DATA_INICIO DATA_FIM TEMPO_EXECUCAO

regards

vtsguerrero
Contributor

Well, I don't know what happened with the conversion, but still doesn't show anything here, look:

index=* sourcetype=* DATA_INICIO=* DATA_FIM=* 
| eval DATA_INICIO_EPOCH= strptime(DATA_INICIO,"%d/%m/%Y-%H:%M:%S") 
| eval DATA_FIM_EPOCH = strptime(DATA_FIM,"%d/%m/%Y-%H:%M:%S") 
| eval TEMPO_EXECUCAO = (DATA_FIM_EPOCH - DATA_INICIO_EPOCH)
| eval TEMPO_EXECUCAO = strftime(difference_epoch,"%d/%m/%Y-%H:%M:%S") 
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO

Shows this table:

 DATA_INICIO                      DATA_FIM                                TEMPO_EXECUCAO
 26/02/2015-04:07:06             26/02/2015-05:01:43     
0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...