Archive

Can you help me change the format of this date field?

sheaross
Explorer

I have a modify date field in my ingested data. The date format of this field is MMDDYY with no "/" or "-".

Is there a way to change the format of this field during search to where it can be MM/DD/YYYY?

Also, I want the search to be able to read this modify field to only search and display events from the previous day. The ingested data is a complete file with only new entries added to the ingested data, so I can't use the time picker for last 24 hours. It will retrieve the entire file.

Tags (1)
0 Karma
1 Solution

Vijeta
Influencer

You can use this

|eval c=len(<yourdatefieldname>)|eval y="0".<yourfieldname>|eval x = if(c=5,y,<yourfieldname>)|eval date=strftime(strptime(x,"%m%d%y"),"%Y/%m/%d")

View solution in original post

Vijeta
Influencer

You can use this

|eval c=len(<yourdatefieldname>)|eval y="0".<yourfieldname>|eval x = if(c=5,y,<yourfieldname>)|eval date=strftime(strptime(x,"%m%d%y"),"%Y/%m/%d")

View solution in original post

493669
Super Champion

In search query you can change time format-

...|eval date=strftime(strptime(<yourfieldname>,"%m%d%y"),"%m/%d/%Y")

also if you use TIME_FORMAT stanza in props.conf to set your time field as _time then you can use time range to search data...so that _time will be your time field

sheaross
Explorer

That works, for the dates that MMDDYY and I did not mention that some of the date do not have a 0 in front of the month. For example: September is 9 instead of 09. This query will only for months with two digits.
Any other ideas?

0 Karma

493669
Super Champion

you can append leading zero to month if it is not present.-

|eval length=len(<yourfieldname>)|eval mon="0".<yourfieldname> |eval <yourfieldname> = if(length=5,mon,<yourfieldname>)|eval date=strftime(strptime(<yourfieldname>,"%m%d%y"),"%m/%d/%Y")
0 Karma

sheaross
Explorer

This answer worked for the leading zero.

Thank you.

0 Karma