Splunk Search

How to change the format of a date from "ddmmyyyy" to "dd/mm/yyyy" in a search?

Motivator

Hi,

I wonder whether someone could help me please.

I'm using a date field in the format ddmmyyyy

Could someone tell me please is there a way by which this can be re-formatted to dd/mm/yyyy

Many thanks and kind regards

Chris

1 Solution

Builder

Hi,

As this is a separate solution, I post it separately. Based on your comment that you have dates from before 1970, I'm afraid you can't use the normal time-functions in Splunk, as they are all based on the number of seconds since 1970-01-01. You can do this using the sed-mode of rex, though:

... |rex mode=sed field=cidDOB "s/(\d\d)(\d\d)(\d\d\d\d)/\1\/\2\/\3/g" 

HTH

View solution in original post

Path Finder

Hello IRHM73,
I had the similar task to perform a few days ago;
I did:
convert timeformat="%d%m%Y" mktime(your_date) AS IntermediateTime // it will convert your date to a time integer
convert timeformat="%d/%m/%Y" ctime(IntermediateTime) AS NewTime // it will format your date with a "/" as you want

Motivator

Hi @clorne, thank you very much for this it is greatly appreciated.

Kind Regards

Chris

0 Karma

Builder

Hi,

As this is a separate solution, I post it separately. Based on your comment that you have dates from before 1970, I'm afraid you can't use the normal time-functions in Splunk, as they are all based on the number of seconds since 1970-01-01. You can do this using the sed-mode of rex, though:

... |rex mode=sed field=cidDOB "s/(\d\d)(\d\d)(\d\d\d\d)/\1\/\2\/\3/g" 

HTH

View solution in original post

Builder

I'm glad it worked for you!
rex is used as the sed-command, familiar from UNIX-like operating systems. Looking at the the expression, you'll see it consists of two parts, separated by slashes. (Disregard the slashes with a backslash in front).

The first part "(\d\d)(\d\d)(\d\d\d\d)" contains three capture groups, each surrounded by parentheses. The \d signifies a digit. So, we are first capturing two digits, then another two digits and finally four digits. The capture groups save the matched strings for future reference.

The second part replaces the string matched in the first part. The special notation of a backslash followed by a number references a string saved by the capture groups in the first part. So, \1 references the first two digits, the \2 references the next two digits, and so on. Since the slash character / is used by rex to limit the expression, the slash needs to be escaped by a backslash, \. So reading out the second part it means "insert the first string you captured, then a slash, then the second captured string, then a slash, then the third captured string"

Motivator

Hi @echalex, that works great, thank you for coming back to me with this,

Because I'm very keen to learn from this could you possibly provide an explanation.

Many thanks and kind regards

Chris

0 Karma

Builder

Hi,

This should do the trick for you (without changing the value itself)

... |fieldformat date_field=strftime(strptime(date_field,"%d%m%Y"),"%d/%m/%Y")

If you want to change the value itself, you would want to use strftime in eval.

HTH.

Motivator

HI @somesoni2, thank you for taking the time to come back to me with this, but unfortunately this doesn't change the format of the field.

As per my comment to @echalex, I'm not sure whether this makes any difference, but a pre cursor to the format change is this line:

 rex field="detail.input-ida-request" "\"dateOfBirth\":{\"value."\:"\"(?<idaDOB>[^\"]+)"

Many thanks and kind regards

Chris

0 Karma

Builder

Edited: you want capital %Y for four-digit years (2015), lower case for two-digit (15).
Edited 2: I did not catch first that the current value only contains the date. In that case, strftime is not sufficient. Therefore, use strftime and strptime.

0 Karma

Motivator

Hi @echalex, thank you for coming back to me with this and for making the amendment but unfortunately this doesn't work.

The format of my field remains as ddmmyyyy.

I'm not sure whether this makes any difference but the pre cursor to this is this line where I'm extracting the date field which I then want to reformat.

rex field="detail.output-cid-response" "\"dateOfBirth\":\"(?<cidDOB>[^\"]+)"  

Many thanks and kind regards

Chris

0 Karma

Builder

Hi Chris,

Nope, the precursor should not make a difference. Perhaps you can post some sample values of cidDOB? Because, with my samples it does work. Perhaps it's clearer with two evals:

... |eval temp_stamp=strptime(cidDOB, "%d%m%Y") |eval new_cidDOB=strftime(temp_stamp,"%d/%m/%Y")

temp_stamp is now a timestamp as number of seconds since 01/01/1970. You'll see the functions are essentially the same, but this example spells it out a bit more.

0 Karma

Motivator

HI @echalex, I really appreciate you coming back to me with this.

I've tried the latest solution you sent and although it works for some, it doesn't work for all new_cidDOB fields.

I've provide a list below highlighting the dates which do and don;t work.

06091991 converts to 06/09/1991
01041993 converts to 01/04/1993
15081985 converts to 15/08/1985
10031966 Doesn't Convert
04051960 Doesn't Convert

I hope this helps.

Many thanks and kind regards

Chris

0 Karma

Builder

Hi Chris,

Oh, you have dates before 1970... Unfortunately, this is going to cause problems for time-related functions, as the timestamp is represented as number of seconds since 1 January 1970 00:00. You could play around with first adding something like a thousand years and later subtracting it.

I suppose your easiest option is to use rex in sed-mode.

0 Karma

SplunkTrust
SplunkTrust

And if you want to change the value just replace fieldformat with eval. like this

... |eval date_field=strftime(strptime(date_field,"%d%m%Y"),"%d/%m/%Y")
0 Karma