strftime and strptime functions are not working in search queries

[ygkr]

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.

[DMohn]

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

[martin_mueller · Accepted solution]

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.

[martin_mueller]

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?

[ygkr]

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.

[DMohn]

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.

[ygkr]

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????

[ygkr]

Thanq DMohn.... Its working 🙂

[ygkr]

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'