ISO 8061 string field- Is it better to do an extraction or conversion?

[Mena]

Hello-

I am attempting to create a heat gauge off the average of two timestamp fields to determine average time an issue was worked. I'm running into issues as these fields are stored as strings in the ISO 8061 format.

I'd like to know if there's a good way to convert this string as simply as possible or to be able to extract certain portions of the string to be able to use a numeric value from the average calculations (ideally extract the MM:SS from the ISO string.)

Thanks!

[bowesmana · Accepted solution]

strptime to parse date/time

| eval t1=strptime(time_1, "%FT%T")
| eval t2=strptime(time_2, "%FT%T")

%F parses date (YYYY-MM-DD) and %T parses time (HH:MM:SS)

then you have numbers and can do with them what you want.

if if you just want the MM:SS as seconds, use

| eval seconds = tonumber(substr(time_1, 15, 2)) * 60 + tonumber(substr(time_1, 18, 2))

 but that of course will ignore hours/days etc.

[Mena]

Thank you so much!