how to chnage "mmss" format strings to mm:ss format

[K2]

I have searched this but I have not found a suitable answer yet,

Here I have a field as below

time
"0"
"7"
"56"
"101"
"3045"
"7034"

These show a time stamp, 0 is 0 second, 56 is 56 seconds, 101 is 1m1s, 3045 is 30m45s.
I would like to transform these to mm:ss format,  so "0" would be "00:00", "101" would be "01:01". 

How I can do this? 

[rnowitzki · Accepted solution]

Hi @K2 ,

This should work if you only have 1-4 characters in the string. If it's more, you would have to extend the case statement.

| eval length=len(value) 
| eval timestamp=case(length=1,"00:0"+value,length=2,"00:"+value,length=3,"0"+substr(value,1,1)+":"+substr(value,2,2),length=4,substr(value,1,2)+":"+substr(value,3,2))
| fields - length

The input field is "value" in my case. I just saw that yours is called "time", looking at your table, so you will have to exchange "value" with "time" in the SPL...

Hope it helps

BR
Ralph
--
Karma and/or Solution tagging appreciated.

[K2]

Hi @rnowitzki ,

Thank you!! Amazing line works so good, I would like to give you my appreciation again.

Regards,
K2