topic Re: How to change time into seconds? in Splunk Search

How to change time into seconds?

nilaksh92 — Fri, 05 May 2017 11:42:53 GMT

Hi Everyone,

Please help me out to convert time format into seconds.

My time field has values like :07, 7:45.

Example:
:07 = 7 secs
7:45= 465 sec

Thanks in advance.
Nikks

Re: How to change time into seconds?

cmerriman — Fri, 05 May 2017 13:02:38 GMT

have you tried the convert function?
https://docs.splunk.com/Documentation/Splunk/6.5.3/SearchReference/Convert

you might first need to put all the time into the same format first if they aren't all in M:SS (for instance it looks like the first one is just :SS)

Re: How to change time into seconds?

jordanb93 — Fri, 05 May 2017 14:18:02 GMT

Function Needed:

dur2sec()
Syntax: dur2sec()
Description: Convert a duration format "[D+]HH:MM:SS" to seconds. You can use wild card characters in the field name.

Re: How to change time into seconds?

DalJeanis — Fri, 05 May 2017 14:55:40 GMT

This assumes that the data can also contain hour:min:sec.

| makeresults | eval mydata=":01 56:23 3:12:15"| makemv mydata | mvexpand mydata
| rename COMMENT as "The above just makes test data"

| rex field=mydata "(?<myHourOrMin>[^:]+)?:?(?<myMin>[^:]+)?:(?<mySec>\d+)"
| eval seconds=3600*if(isnull(myMin),0,coalesce(myHourOrMin,0))+60*coalesce(myMin,myHourOrMin,0)+coalesce(mySec,0)

If it can only contain minutes and seconds, then this simpler version will meet the need...

| makeresults | eval mydata=":01 56:23 2:15 :42 "| makemv mydata | mvexpand mydata
| rename COMMENT as "The above just makes test data"

| rex field=mydata "(?<myMin>[^:]+)?:(?<mySec>\d+)"
| eval seconds=60*coalesce(myMin,0)+coalesce(mySec,0)

... and here's another way, that pretty much works for anything...

| makeresults | eval mydata=":01 56:23 2:15 :42 "| makemv mydata | mvexpand mydata
| rename COMMENT as "The above just makes test data"

| rex field=mydata "(?<myHourOrMin>[^:]+)?:?(?<myMin>[^:]+)?:(?<mySec>\d+)"
| eval seconds=3600*if(isnull(myMin),0,coalesce(myHourOrMin,0))+60*coalesce(myMin,myHourOrMin,0)+coalesce(mySec,0)

| rename COMMENT as "Here's another way..."
| eval mydata2 = substr("00:00:00",1,8-len(mydata)).mydata
| convert dur2sec(mydata2) as seconds2

Re: How to change time into seconds?

DalJeanis — Tue, 09 May 2017 14:49:37 GMT

Actually dur2sec() requires the hour and minute, so see the very last example in my answer for how to make it work in this case.

Re: How to change time into seconds?

nilaksh92 — Thu, 11 May 2017 06:26:42 GMT

Thanks DalJeanis.

It worked. Thanks for your kind help.