How To Get Datetime Difference?

[vtsguerrero]

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  

[dwaddle]

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 · Accepted solution]

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]

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

[vtsguerrero]

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

[emiller42]

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

[gfuente]

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]

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