Getting Data In
Highlighted

## How To Get Datetime Difference?

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
``````
Tags (3)
1 Solution
Highlighted

## Re: How To Get Datetime Difference?

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

Highlighted

## Re: How To Get Datetime Difference?

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
``````
Highlighted

## Re: How To Get Datetime Difference?

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)

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
``````
Highlighted

## Re: How To Get Datetime Difference?

Contributor

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

Highlighted

## Re: How To Get Datetime Difference?

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

## Re: How To Get Datetime Difference?

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

## Re: How To Get Datetime Difference? 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
``````