topic Re: strftime and strptime functions are not working in search queries in Splunk Search

strftime and strptime functions are not working in search queries

ygkr — Fri, 09 Sep 2016 11:04:56 GMT

I have multiple time fields in my db like Reported Date, Last Modified Date, Responded Date.. If I apply strftime/strptime functions on that it is not working someone plz tell me how to do that.

Re: strftime and strptime functions are not working in search queries

martin_mueller — Fri, 09 Sep 2016 12:33:07 GMT

I'm guessing your fields are literally called Reported Date, and you tried strptime(Reported Date, "%...")?

If so you will need to enclose the field names in single quotes: strptime('Reported Date', "%...")
Whenever possible you should use field names with alphanumeric and underscore characters to ease eval use. For a worse example, Report+Date would be a valid field name... but eval would interpret that as "Report field plus Date field" without the single quotes.

Re: strftime and strptime functions are not working in search queries

ygkr — Tue, 29 Sep 2020 10:55:22 GMT

Thanks for the response martin... 🙂
I tried with single quotes even though my datetime fields are not working with strftime and strptime functions as I expected...!!
It is working with only _time field but my requirement is to work with all the datetime fields in the given data like('Reported Date', 'Last_Modified_Date' and 'Last_Resolved_Date'

Re: strftime and strptime functions are not working in search queries

martin_mueller — Mon, 12 Sep 2016 22:19:17 GMT

Well, I could keep guessing different things that you may or may not be doing wrong... or you could supply more information. What do those fields look like? What searches have you tried that failed? What does a failed result look like?

Re: strftime and strptime functions are not working in search queries

ygkr — Tue, 29 Sep 2020 10:57:27 GMT

source="TDA_APA_Excel Data.csv" host="CDC1-D-6QKN7BS" index="test" sourcetype="test"
| eval time = strftime(_time, "%Y")
| dedup time
| table time

O/P
time↕
2016

2015

2014

2013

2012

2011

This is wt I tried to get the same output as I got for "_time" field as shown above.

source="TDA_APA_Excel Data.csv" host="CDC1-D-6QKN7BS" index="test" sourcetype="test"
| eval time = strftime('Last_Modified_Date', "%Y")
| dedup time
| table time

Obtained O/P: No results found.

Re: strftime and strptime functions are not working in search queries

DMohn — Tue, 13 Sep 2016 11:30:49 GMT

What are the original values of your field Last_Modified_Date? Is it a Unix timestamp or some other date format?

strftime requests the time field to be a Unix timestamp, otherwise it does not know how to transform the date.

Re: strftime and strptime functions are not working in search queries

ygkr — Tue, 29 Sep 2020 10:58:28 GMT

Hi DMohn thanks for the response,...
The dataformat for the field Last_Modified_Date is DateTime.
It is not unix timestamp... plz let me knw hw to convert that field to unix timestamp dataformat????

Re: strftime and strptime functions are not working in search queries

DMohn — Tue, 29 Sep 2020 11:00:54 GMT

Taking the information from your last comment (Last_Modified_Date being SQL DateTime format) you will have to convert this date into a Unix Timestamp by using strptime before being able to use strftime again.

If your Last_Modified_Date looks like 2016-09-01 10:00:00 (YYYY-MM-DD HH:MM:SS) you may use the following conversion to only have the year (I assume thats what you want):

 your_base_search | eval year=strftime(strptime(Last_Modified_Date,"%Y-%m-%d %H:%M:%S"),"%Y")

You may refer to https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commontimeformatvariables for more information on the date and time variables of strptimeand strftime

Re: strftime and strptime functions are not working in search queries

ygkr — Wed, 14 Sep 2016 09:12:49 GMT

Thanq DMohn.... Its working 🙂