topic Re: How to combine two separate date and time fields into one timestamp field? in Splunk Search

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

bcusick — Mon, 28 Sep 2020 17:27:53 GMT

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"

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

kbecker — Mon, 28 Sep 2020 17:27:58 GMT

You can use the eval function for example

| eval combined = TRADE_YYMMDD." ".EXEC_TIME_HHMMSS

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

bcusick — Wed, 03 Sep 2014 00:10:29 GMT

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?

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

kbecker — Wed, 03 Sep 2014 00:31:22 GMT

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

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

kristian_kolb — Mon, 28 Sep 2020 17:28:45 GMT

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

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

bcusick — Thu, 04 Sep 2014 18:37:50 GMT

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

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

kristian_kolb — Thu, 04 Sep 2014 19:18:55 GMT

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