Splunk Search

How to convert string to date?

jimbolya11
New Member

I have an existing column "Date" and I need to convert it from a string like 4/2/2018 to a date of 4/2/2018.

I've tried some of the answers but none of them have worked so far.

Tags (3)
0 Karma

jconger
Splunk Employee
Splunk Employee

Here is how to do it in a search:

| makeresults 
| eval Date="4/2/2018" 
| eval timestamp=strptime(Date, "%m/%d/%Y") 
| eval formattedTimestamp = strftime(timestamp,"%Y-%m-%dT%H:%M:%S.%Q")

Is the "Date" field what you want to use as the timestamp of the event? If so, timestamp recognition options -> http://docs.splunk.com/Documentation/Splunk/latest/Data/Configuretimestamprecognition

jimbolya11
New Member

Thanks...
I must not be explaining my self correctly.

I import a csv file. Splunk automagically puts a _time field into the dataset. This _time field is not what I want to use. I want to use the Date field that was already in the csv during import. Problem is that whole column is a string and not recognized as date. Therefore I cannot specify date ranges in a search with it.

So far I've had some very limited luck with this string:

index="dor_test" source="filename.csv" | eval Date=strptime(Date,"%m/%d/%Y") | fieldformat Date = strftime(Date, "%d-%b-%Y") | eval _time=Date

0 Karma

macadminrohit
Contributor

try this :

| makeresults 
 | eval Date="4/2/2018" 
 | eval timestamp=strftime(strptime(Date, "%m/%d/%Y"),"%m/%d/%Y") | eval _time=timestamp
0 Karma

tpeterson2
Engager

I had the same issue when trying to parse a date string from an input lookup. The following worked for me based on your example.

index="dor_test" source="filename.csv" 
| convert mktime(Date) as Date timeformat="%m/%d/%Y"
| eval Date=strftime(Date,"%m/%d/%Y")

Please note that the timeformat needs to match the incoming format. e.g.
4/2/2018 = "%m/%d/%Y"
4/2/18 = "%m/%d/%y"

Hope that helps.

Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...