How do I convert the CSV field "8:13:29.9299730 PM" to seconds with no date or AM/PM?

[mdwecht]

I just downloaded 6.2.3 for Windows x64 and want Splunk to have _time = seconds i.e. 8:13:29.9299730 = 29609.9299730

The CSV RAW data is as follows: "8:13:29.9299730 PM"

The default converted view is as follows: _time = 6/6/15 8:13:29.929 PM

I don't want the month, date, year, and AM/PM. I would like all 7 decimal places. I am new to Splunk 🙂

[acharlieh · Accepted solution]

_time is stored in Splunk as seconds since the UTC epoch, it's just displayed as a friendly string. You can rename the _time field or use eval to copy it to another field name to see this.

Getting my Splunk to do a one off at parsing what you have there:

| noop | stats count | eval _time = strptime("8:13:29.9299730 PM","%I:%M:%S.%7N %p") | eval foo = _time

foo currently gives me: 1433621609.929973 since the timezone on my Splunk instance and user are UTC other timezones will be different.

Now the below assumes that Splunk is parsing this field of your CSV input correctly with all decimal places. If it is not, you could tweak those settings in props.conf and re-index your data, or if the original string is in a different field or in the _raw you could use strptime as I did above to get _time with all decimal places at search time with no reindexing. (strptime and relative_time below are just a few of the Common Functions for eval and where)

Now judging from the value you are stating that you want it looks like you want the number of seconds since noon but I'm guessing since your raw data may also have AM values perhaps you want the number of seconds since either noon or midnight. This could be accomplished like so:

| eval sinceMidnight = _time - relative_time(_time,"@d") | eval sinceNoon = _time - relative_time(_time,"@d+12h") | seconds = if(sinceNoon<0,sinceMidnight,sinceNoon)

If you actually only wanted the number of seconds since midnight then you would only need the first eval on this list. Hope this helps your understanding and gives you some interesting things to play with.

[mdwecht]

THX for your response. I appreciate your time. You have given me more to think about. In my application I am only capturing about 6 seconds of data at a time, about 500,000 timestamped lines, into a CSV file. I actually don't care about AM or PM or time of day at this point. I just need to create a strip chart of events against time.