How to convert milli seconds into duration format HH:MM:SS?

[sjs]

Hey people, I am trying to convert the execution time which I get in ms to duration format

| rex "EXECUTION_TIME : (?<totalTime>[^ms]+)"

I did also try something like this

| eval inSec = inMs / 1000 | fieldformat inSec = tostring(inSec, "duration")

but it is giving me null value

Could you please help me out here

[sjs]

I have an another question, lets have I have table as such

which I generated with this command

.. | DDB_WRITE_EXECUTION_TIME="*" FILTERING_EXECUTION_TIME="*" | transpose 0

is it possible for me to convert all the values of row1 to HH:MM:SS format

[yuanliu]

Yes.  Replace fieldformat with eval. (Use fieldformat allows you to manipulate time numerically while displaying it in a readable format.  But if you want it as column head, which is for displaying purposes, you must convert it to a real string.  eval will do that.)

[scelikok]

Hi @sjs,

You can use below eval;

| eval row1=tostring(row1,"duration")

[yuanliu]

Your title says you want to convert some data originally in miliseconds into a different format.  But you didn't illustrate your data, either the original data, or intermediate data, namely totalTime that you used rex to extract.   Nor did you explain how the field inMs in the tried code relates to totalTime.

I have several suspicions.  First is the rex.  Unless your raw data has some really funky characteristics, the extraction group would be something like (?<totalTime>\d+) (all integers) or (?<totalTime>[\d\.]+) (decimals).  Have you confirmed that totalTime is a numeric value?

If totalTime is a valid numeric field, you can use strftime to reformat it into HH::MM::SS.

| fieldformat totalTime = strftime(totalTime, "%H:%M:%S")

As expected, if totalTime exceeds 24 hours, the reformatted representation will cross 0.

Example,

| makeresults
| fields - _time
| eval totalTime = 12345.6789
| fieldformat totalTime = strftime(totalTime, "%H:%M:%S")

results in

totalTime

19:25:45

whereas

| makeresults
| fields - _time
| eval totalTime = 123456.789
| fieldformat totalTime = strftime(totalTime, "%H:%M:%S")

gives

totalTime

02:17:36

[yuanliu · Accepted solution]

In addition to @ITWhisperer's diagnosis that I missed "milli" in millisecond, there's a separate caveat (which should not show on most search heads but...): locale.  It turns out that Splunk is "too good" at handling timezone that strftime is calculated based on the search head's local time.  As a result, the above formula can give you very funny results if search head is not on UTC.  Whereas most search heads are better off using UTC, there is no guarantee.  I searched in vain to find a reverse function of convert dur2sec().  So, I came up with a really dumb way to brute force output:

| eval offset = strftime(0, "%::z")
| rex field=offset "^(?<sign>[+-])(?<offset>[\d:]+)$"
| eval offset = sign . strptime(offset, "%H:%M:%S")
| fieldformat string_DDB_WRITE_EXECUTIION_TIME = strftime(DDB_WRITE_EXECUTION_TIME/1000 + offset, "%H:%M:%S")

Using your sample data of 4333403ms and my local timezone of -0800, the output is

DDB_WRITE_EXECUTION_TIME	offset	sign	string_DDB_WRITE_EXECUTIION_TIME
4333403	-1673971200.000000	-	01:12:13

[sjs]

Hey I did try your approach. It was great

But there was one problem here, the time was 4333403 ms which when converted to HH:MM:SS should be around 1hr

but the query gave me it as 3hrs

[ITWhisperer]

You need to convert ms to s (by dividing by 1000) before using in strftime

| eval dob_write_execution_time = strftime(DOB_WRITE_EXECUTION_TIME/1000, "%H:%M:%S")

[sjs]

Thanks this helped

[PickleRick]

Rex will give you a string value. You can't divide a string value or convert it to duration. Convert it number with tonumber() first.