How to combine two separate date and time fields into one timestamp field?

[bcusick]

Hi, I have two separate fields that I'd like to combine into 1 timestamp field.

The fields are formatted "YYMMDD" and "HHMMSS"

I'd like to combine and eval them to read "mm/dd/yyyy hh:mm:ss".

Does anyone have any experience with this? The fields are "TRADE_YYMMDD" and "EXEC_TIME_HHMMSS"

[kristian_kolb]

If you have (as @kbecker shows) created a field which holds the combined date and time information, you can get the epoch representation like so;

your_search 
| eval combined_epoch = strptime(combined, "%y%m%d %H%M%S") 
| eval nice_date = strftime(combined_epoch, "%m/%d/%y %H:%M:%S")

Note that it is important that the strptime variables actually match the contents of the field combined. In this case

140823 095421

is ok, but none of the following will work;

20140823 09:54:21
14.08.23 095421
2014-08-23 09:54:21

So basically you need to look at the data you have, and specify how parse the time string into epoch (str*ptime). Then you can decide yourself how you want to **format* the epoch timestamp (str*f*time).

See the docs, or link below for common variables;

http://www.strftime.net

/K

[kristian_kolb]

%e is for days 1..31,
instead of %d, which is 01..31

/k

[bcusick]

So far so good on this one. The only discrepency I am seeing is the fact that it contains a "zero" in the month if it's a single-digit month. Is there any way to get rid of that?

sourcedata: 140823 090421

New field: 08/23/2014 09:54:21 AM

And what I'm looking for is: 8/23/2014 9:54:21 AM. It just needs to be an exact match to an already existing field in this format. Thanks

[kbecker]

You can use the eval function for example

| eval combined = TRADE_YYMMDD." ".EXEC_TIME_HHMMSS

[kbecker]

After you combine the fields use convert mktime to convert the time from human readable to epoch.

http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/Convert

[bcusick]

Thanks..I can combine the fields, but how about getting them into epoch? I fear that just throwing them together won't give me the correct time that I can convert correctly..or am I looking too far into this?