Splunk Search

Trouble comparing timestamps

ShagVT
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
1 Solution

harshpatel
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

harshpatel
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

ShagVT
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

woodcock
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

ShagVT
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

ShagVT
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. 60*60*4 = 14400, the amount the answer is off.

0 Karma

Vijeta
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

ShagVT
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

ShagVT
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

Vijeta
Influencer

oh yes, sorry was a typo

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, ...