topic Re: Convert string to date in Splunk Search

Convert string to date

shayhk — Tue, 24 Dec 2013 11:43:13 GMT

Hi, I am tring to convert string data to date and find diff second
the problem is that i cant convert the string to date

...
|table Key DateTime1 DateTime2

Datetime1&2 formats are [2013-12-17 09:38:57.7667] and they are strings

i want to find the diff seconds between them

Re: Convert string to date

shayhk — Tue, 24 Dec 2013 14:59:07 GMT

I tried

host=...
| table DateTime1

| convert timeformat="%Y-%m-%d %T" mktime(DateTime1) as _time

but the _time column is empty

the DateTime value is [2013-12-17 09:38:57.7667]

Re: Convert string to date

jsie_splunk — Tue, 24 Dec 2013 19:21:00 GMT

Can you provide a raw example of the event? Are you intending to handle the "57" in the above string as the seconds? Or "57.7667"?

Re: Convert string to date

sciurus — Thu, 26 Dec 2013 09:41:07 GMT

Is the [ and ] part of the actual value, or are you adding that in to the question? If it's part of the value, timeformat probably needs to know.

Re: Convert string to date

somesoni2 — Thu, 26 Dec 2013 12:46:44 GMT

Have you tried ...|eval DateTime1=strptime(DateTime1,"%Y-%m-%d %H:%M:%S.%3Q")?

Re: Convert string to date

Lowell — Thu, 26 Dec 2013 21:58:49 GMT

<your search>
  | rex " (?<dt1>[0-9-]+ [0-9:.]+) (?<dt2>[0-9-]+ [0-9:.]+)"
  | eval dt1=strptime(dt1,"%Y-%m-%d %H:%M:%S.%3Q")
  | eval dt2=strptime(dt2,"%Y-%m-%d %H:%M:%S.%3Q")
  | eval diff=dt2-dt1

Re: Convert string to date

matthewcanty — Wed, 08 Jan 2014 11:53:19 GMT

More info:
Eval Functions
http://docs.splunk.com/Documentation/Splunk/6.0.1/SearchReference/CommonEvalFunctions
Time Formats
http://docs.splunk.com/Documentation/Splunk/6.0.1/SearchReference/Commontimeformatvariables

Re: Convert string to date

vgdhavale10 — Wed, 27 May 2015 09:22:27 GMT

Thanks @Lowell.It worked in my case..