How to change time into seconds?

[nilaksh92]

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

[DalJeanis · Accepted solution]

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

[nilaksh92]

Thanks DalJeanis.

It worked. Thanks for your kind help.

[cmerriman]

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)

[jordanb93]

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.

[DalJeanis]

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.