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.
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
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
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.
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
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?
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.
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
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?
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?
oh yes, sorry was a typo