TimeFormat conversion to millisecond

[hemendralodhi]

Hello,

I have extracted field which contains application response time in below format.

Format:

00:00:00.000
00:00:00.003
00:00:00.545
00:00:01.053
00:00:29.544

I need to convert it into millisecond or second. I tried using strptime and convert function but not working as expected. Can someone please advise?

Thanks
Hemendra

[javiergn · Accepted solution]

I would simply use rex for that (assuming your field name is myfield):

| rex field=myfield "(?<hour>\d{2}):(?<minute>\d{2}):(?<second>\d{2})\.(?<millisecond>\d{3})"
| eval durationInSeconds = hour * 3600 + minute * 60 + second + millisecond/1000
| eval durationInMilliseconds = durationInSeconds * 1000

Example:

| stats count | fields - count
| eval myfield = "01:01:29.544"
| rex field=myfield "(?<hour>\d{2}):(?<minute>\d{2}):(?<second>\d{2})\.(?<millisecond>\d{3})"
| eval durationInSeconds = hour * 3600 + minute * 60 + second + millisecond/1000
| eval durationInMilliseconds = durationInSeconds * 1000

Output:

durationInSeconds
3689.544000 

durationInMilliseconds  
3689544.000 

[hemendralodhi]

Thanks javiergn for your quick response. I tried with your method but it seems I am getting 2 values and also some are missing:

Here is the actual field value:

00:00:00.000
00:00:00.002
00:00:00.003
00:00:00.005
00:00:00.006

Here is the Result:

durationInSeconds durationInMilliseconds
0.123000 123.000
0.123000 123.000
0.109000 109.000
0.109000 109.000
0.148000 148.000
0.148000 148.000
0.043000 43.000
0.043000 43.000
0.084000 84.000
0.084000 84.000
0.143000 143.000
0.143000 143.000
0.033000 33.000
0.033000 33.000

[hemendralodhi]

Look like my extracted field is behaving differently. When ran your rex search to see values in field got below. Not Sure why it is coming like this. Is it possible that it is related to string or numerical field value?

hour minute second millisecond
00 00 00 123
00 00 00 123
00 00 00 109
00 00 00 109
00 00 00 148
00 00 00 148
00 00 00 043
00 00 00 043
00 00 00 084
00 00 00 084
00 00 00 143
00 00 00 143

[javiergn]

Hi,

Sorry I'm confused. Can you post the exact query you are running please (ensure you are using the code sample button otherwise it will escape some symbols) and also how your raw data looks like?

In any case, I have tried replicating your example above and it seems to be working fine:

| stats count | fields - count
| eval myfield = split("00:00:00.000, 00:00:00.002, 00:00:00.003, 00:00:00.005, 00:00:00.006", ",")
| mvexpand myfield
| rex field=myfield "(?<hour>\d{2}):(?<minute>\d{2}):(?<second>\d{2})\.(?<millisecond>\d{3})"
| eval durationInSeconds = hour * 3600 + minute * 60 + second + millisecond/1000
| eval durationInMilliseconds = durationInSeconds * 1000

Output: see picture below

[hemendralodhi]

Great it worked now.

I was checking the result using values(Field) and this was reordering the results.

Thanks for your help.

[javiergn]

No worries.
Please don't forget to mark it as answered so that others can benefit from it.

Regards,
J