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.

0 Karma
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
0 Karma
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?

0 Karma
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?

0 Karma
Highlighted

Re: Trouble comparing timestamps

Influencer

oh yes, sorry was a typo

0 Karma
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?

0 Karma
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.

0 Karma
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

View solution in original post

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.

0 Karma