Calculate time difference with extracted fields and offset time zones

[mrhodes93]

I've got logs that contain a timestamp in 24 hour YYYY-MM-DD HH:MM:ss:SSS format (example: 2021-04-29 18:43:07.557).  The timestamp in this log message is +5 hours ahead of the _time of the event.  

So far I've got this much, which extracts the timestamp from the message but I don't know how to go about showing the difference between these two, especially with the five hour offset.  Ideally would just like to show a third value of the difference in the table.  Appreciate any instruction. 

sourcetype="PCF:log" cf_app_name=app1 (msg="*message query here*")
| rex field=msg "created on\s+(?<lockTime>\S+\s+\S+)"
| table _time,lockTime

_time	lockTime	Expected
2021-04-28 12:46:37.381	2021-04-28 17:46:33.961	00:00:03.420

I should mention too that only the time portion, not the date, will need the difference calculated.  The YYYY-MM-DD will always be the same between _time and lockTime. 

[richgalloway · Accepted solution]

The only way to calculate a difference between time fields is to convert them into integers using strptime.

sourcetype="PCF:log" cf_app_name=app1 (msg="*message query here*")
| rex field=msg "created on\s+(?<lockTime>\S+\s+\S+)"
| eval elockTime = strptime(lockTime, "%Y-%m-%d %H:%M:%S.%3N")
| eval Expected = elockTime - _time - (5*3600)
| table _time,lockTime, Expected