Splunk Search

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

bcusick
Communicator

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"

Tags (4)
0 Karma

kristian_kolb
Ultra Champion

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
Ultra Champion

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

/k

bcusick
Communicator

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

0 Karma

kbecker
Communicator

You can use the eval function for example

| eval combined = TRADE_YYMMDD." ".EXEC_TIME_HHMMSS

kbecker
Communicator

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
Communicator

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?

0 Karma
Get Updates on the Splunk Community!

New Splunk Observability innovations: Deeper visibility and smarter alerting to ...

You asked, we delivered. Splunk Observability Cloud has several new innovations giving you deeper visibility ...

Synthetic Monitoring: Not your Grandma’s Polyester! Tech Talk: DevOps Edition

Register today and join TekStream on Tuesday, February 28 at 11am PT/2pm ET for a demonstration of Splunk ...

Instrumenting Java Websocket Messaging

Instrumenting Java Websocket MessagingThis article is a code-based discussion of passing OpenTelemetry trace ...