String to Date Time

[dhiraj027in]

I am new to splunk and currently trying to get the date and time difference (Opened vs Resolved) for an incident.
Based on the field type Opened & Resolved are string type and what should I do? I have gone to multiple answers but not able to figure out the solution. Please help.

Below is the example of my selected fields

Assigned to = Arjay Torreliza
Category = Alert
Number = INC0975300
Opened = 5/31/2016 22:43
Resolved = 6/1/2016 9:49
host = prd-p-ttsrqrml973d
source = incident.csv
sourcetype = csv

[sundareshr · Accepted solution]

First, you need to convert the string to epoch time using the strptime command & then find the difference.. try this

| eval opened_epoch=strptime(Opened, "%-m/%d/%Y %H:%M) | eval resolved_epoch=strptime(Resolved, "%-m/%d/%Y %-H:%M) | eval duration=tostring(resolved_epoch-opened_epoch, "duration")

[dhiraj027in]

Great. This works but the duration is not exactly what I am looking for. Is this possible?

Current output (this shows 6 days, 23 hours & 23 seconds)
6+23:23:00.000000

What I am looking for (167 hours and 23 seconds)
167:23:00.000000

[sundareshr]

Add this to the end

| rex field=z "(?<d>\d{0,2})\+?(?<h>\d{2})(?<ms>:\d{2}:\d{2})" | eval d=if(len(d)=0, 0, d) | eval duration=(d*24+h).ms 

[dhiraj027in]

I used the above and integrated with my query. I am not getting any output for "duration". Should I change anything here?

index="incidents_stores_wisp" date_year="2016" Category="Alert" | eval opened_epoch=strptime(Opened, "%m/%d/%Y %H:%M") | eval resolved_epoch=strptime(Resolved, "%m/%d/%Y %H:%M") | eval duration=tostring(resolved_epoch-opened_epoch, "duration") | rex field=z "(?\d{0,2})\+?(?\d{2})(?:\d{2}:\d{2})" | eval d=if(len(d)=0, 0, d) | eval duration=(d*24+h).ms | table Number "Assigned to" "Short description" Opened Resolved duration