Splunk Search
Highlighted

## Trouble comparing timestamps

Path Finder

I have two timestamps in different formats and I want to see how much time has elapsed between them. I have a rex that has already parsed the fields into timestampA and timestampB. They look like this:

timestampA: 2019-04-11 14:22:36.978 PDT
timestampB: 2019-04-11T21:22:36.907Z

I'd like to be able to calculate, say, the number of milliseconds between these. (A will always be later than B.) In this example, the correct answer would be 71.

I've tried a couple of approaches here:

Attempt 1

``````<my query> | convert timeformat="%YYYY-%mm-%dd %HH:%MM:%SS.%3Q %Z" mktime(timetampA) as a| convert timeformat="%YYYY-%mm-%ddT%HH:%MM:%SS.%3Q%Z" mktime(timestampB) as b | eval diff=a - b | table _raw, timestampA, a, timestampB, b, diff
``````

Attempt 2

``````<my query> | eval a=strptime(timetampA, "%YYYY-%mm-%dd %HH:%MM:%SS.%3N %Z") | eval b=strptime(timetampB, "%YYYY-%mm-%ddT%HH:%MM:%SS.%3N%Z") | eval diff=a- b| table _raw, timetampA, a, timetampA, b, diff
``````

Neither works. In fact, "a", "b", and "diff" print as blanks in my table, so I must be missing something pretty fundamental.

Tags (3)
1 Solution
Highlighted

## Re: Trouble comparing timestamps

Influencer

It works for me , I tried with below query and it works and gives diff as 71

``````| makeresults | eval time="2019-04-11 14:22:36.978 PDT" , timeA="2019-04-11T21:22:36.907Z"| eval time=strptime(time,"%Y-%m-%d %H:%S:%M.%3N %Z")| eval timeA=strptime(timeA,"%Y-%m-%dT%H:%S:%M.%3NZ")|eval diff= time - timeA
``````
Highlighted

## Re: Trouble comparing timestamps

Path Finder

When I run this, I get these results:

diff=-14399.929000
time=1555018582.978000

timeA=1555032982.907000

So this seems to be an improvement (since you are actually getting results) but the answer seems to be wrong ... it should end up as 71 ms for these two timestamps. Is the format not handling the Zulu time correctly?

Highlighted

## Re: Trouble comparing timestamps

Path Finder

harshpatel has a working solution below which is very similar to yours. I think the problem is your timestamp format ... you have H:S:M instead of H:M:S?

Highlighted

## Re: Trouble comparing timestamps

Influencer

oh yes, sorry was a typo

Highlighted

## Re: Trouble comparing timestamps

Esteemed Legend

It is possible that the `timestampA` and `timestampB` fields were created using `fieldformat` and not `eval`. Study the search below and notice that only one of the `diff` works!

``````| makeresults
| eval strtimeA="2019-04-11 14:22:36.978 PDT"
| eval strtimeB="2019-04-11T21:22:36.907Z"
| fieldformat fstrtimeA=strptime(strtimeA,"%Y-%m-%d %H:%S:%M.%3N %Z")
| fieldformat fstrtimeB=strptime(strtimeB,"%Y-%m-%dT%H:%S:%M.%3NZ")
| eval estrtimeA=strptime(strtimeA,"%Y-%m-%d %H:%S:%M.%3N %Z")
| eval estrtimeB=strptime(strtimeB,"%Y-%m-%dT%H:%S:%M.%3NZ")
| eval eftimeA=fstrtimeA
| eval eftimeB=fstrtimeB
| eval strdiff= strtimeA - strtimeB
| eval fstrdiff= fstrtimeA - fstrtimeB
| eval estrdiff= estrtimeA - estrtimeB
| eval efdiff= eftimeA - eftimeB
``````
Highlighted

## Re: Trouble comparing timestamps

Path Finder

timestampA and timestampB were created neither by fieldformat nor eval ... I'm extracting them using rex. I'm not sure how that works under the covers.

When I'm looking at the results of your query, it seems to end up with the same problem as the previous answer ... it comes up with estrdiff = -14399.929000. But from simply looking at the timestamps, it clear that the correct answer is 71 ms, so I'm not sure where the disconnect is?

Highlighted

## Re: Trouble comparing timestamps

Path Finder

It looks like you copied Vijeta's bad format with H:S:M? Even fixing that format though, I still get the wrong answer?

I think Harshpatel missed the second important detail ... the trailing Z on timestampB also needs a % to get the timezone correct. Otherwise is it just using my local timezone? (EDT)

That seems to make sense of the result ... since EDT is 4 hours behind GMT. 60604 = 14400, the amount the answer is off.

Highlighted

## Re: Trouble comparing timestamps

Contributor

Hi @ShagVT

Hope this solves your problem ( modified your second attempt query):

``````| makeresults count=1
| eval timestampA = "2019-04-11 14:22:36.978 PDT"
| eval timestampB = "2019-04-11T21:22:36.907Z"
| eval a=strptime(timestampA, "%Y-%m-%d %H:%M:%S.%3N %Z")*1000
| eval b=strptime(timestampB, "%Y-%m-%dT%H:%M:%S.%3N%Z")*1000
| eval diff=a-b
``````
Highlighted

## Re: Trouble comparing timestamps

Path Finder

Yes! Thank you! I guess I've done timestamp parsing so long in Java and SQL that something like %YYYY just came out automatically. And great catch on the %Z for timestampB.