topic Re: How to change string from csv file to date format for proper sorting in search? in Splunk Search

How to change string from csv file to date format for proper sorting in search?

jwolach2 — Fri, 22 Aug 2014 20:03:09 GMT

I have data that was imported from a .csv file. One of the field in the .csv file is called "date". However, when Splunk imports that field the data is a string in the format 8/22/2014. I would like to sort the data by this "date" field but, Splunk does not sort it correctly because it sees the data as a string and sorts based on left to right.

How can I make it so that I can sort on the "date" data as if it was an actual date?

Re: How to change string from csv file to date format for proper sorting in search?

somesoni2 — Fri, 22 Aug 2014 20:33:40 GMT

You can try this

your base search to get data from csv | eval date=strptime(date,"%m/%d/%Y") | sort 0 date

Re: How to change string from csv file to date format for proper sorting in search?

aweitzman — Fri, 22 Aug 2014 20:35:53 GMT

Somewhere in your search string, add the following clause:

convert timeformat="%m/%d/%Y" mktime(date) as numdate

That will create a new field numdate containing the epoch numerical representation of your date field. You can then sort on numdate.

Re: How to change string from csv file to date format for proper sorting in search?

jwolach2 — Mon, 28 Sep 2020 17:23:35 GMT

Great!!! Thank you very much! So here's what I now have as my search statement.

Now, I would like to select a date range to be displayed. How can I do that with the epoch numerical data?

Re: How to change string from csv file to date format for proper sorting in search?

jwolach2 — Mon, 28 Sep 2020 17:23:41 GMT

Got it, I think.

sourcetype=csv | eval date=strptime(date,"%m/%d/%Y") | eval reformatted_date=strftime(date,"%m/%d/%Y") | sort 0 date AND inv | table date reformatted_date inv customer item vendor description | where (date > strptime("05/31/2013","%m/%d/%Y") AND date < strptime("07/01/2013","%m/%d/%Y"))

Thanks