Getting Data In

Issue to find the format to convert string to date

erwanlebaron
Engager

Hi

I tried to convert some string to date but it doesn't work.

Below an example of date ("Created Time")

Created Time
Friday April 19 2019 5:23:48 PM CEST
Friday April 26 2019 5:22:08 PM CEST
Friday August 16 2019 12:44:31 PM CEST
Friday August 9 2019 5:29:40 PM CEST
Friday December 13 2019 2:11:15 AM CET
Friday December 20 2019 12:32:19 PM CET
Friday December 20 2019 12:32:21 PM CET
Friday December 20 2019 12:42:35 PM CET
Friday December 6 2019 9:35:07 AM CET
Friday February 1 2019 4:14:16 PM CET
Friday February 1 2019 4:25:06 PM CET
Friday February 1 2019 4:36:21 PM CET
Friday February 1 2019 8:27:19 PM CET
Friday February 22 2019 5:17:29 PM CET
Friday February 22 2019 5:17:53 PM CET
Friday February 22 2019 8:18:31 PM CET
Friday February 8 2019 10:03:56 PM CET
Friday February 8 2019 11:00:07 AM CET

I tried the following search

[My Search]
| eval Created_Time_timestamp=strptime("Created Time", "%A %B %e %Y %I:%M:%S %p %Z")
| table "Global Id", "Created Time", Created_Time_timestamp

But "Created_Time_timestamp" stay empty

Global Id Created Time Created_Time_timestamp
4a003a1203468c82b5b16c6877f2bac1 Thursday December 26 2019 10:23:25 AM CET

4a01b7d12e0a96078b31c8c109efe428 Thursday December 26 2019 12:35:38 PM CET

4a036467deb8077f9ce0115fae8115b3 Thursday December 26 2019 10:23:19 AM CET

4a0428dc18231232af4588a95d26ca4a Tuesday January 15 2019 6:01:56 PM CET

4a0435ed94329293bcc019297385fd7a Tuesday January 22 2019 6:02:07 PM CET

4a0915459109146eb0a02f49d5b6946d Thursday December 26 2019 10:23:19 AM CET

4a09af63013a323681b9ea494710d6ae Wednesday January 23 2019 5:03:20 PM CET

4a0a85212b65c1fc9bed8fbddf8f9b58 Thursday January 24 2019 12:58:22 PM CET

4a0b1be513465e5a8bd5379e2226a7e3 Wednesday August 21 2019 12:45:47 PM CEST

I don't succed to find a solution.
Is there someone who could have an idea ?

Thanks

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @erwanlebaron,
strptime command converts a string in epochtime, if you want it in a human readable format (e.g. mm/dd/yyyy HH:MM:SS) you have to use strptime and strtftime, something like this:

 [My Search]
| eval Created_Time_timestamp=strftime(strptime("Created Time", "%A %B %e %Y %I:%M:%S %p %Z"),"%m/%d/%Y %H:%M:%S")
| table "Global Id", "Created Time", Created_Time_timestamp

If it continues to dot run, rename fields with spaces and try again:

[My Search]
| rename "Created Time" AS Created_Time
| eval Created_Time_timestamp=strftime(strptime(Created_Time,"%A %B %e %Y %I:%M:%S %p %Z"),"%m/%d/%Y %H:%M:%S")
| table "Global Id" Created_Time Created_Time_timestamp

Ciao.
Giuseppe

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

The timeformat in your query is fine. The problem is the how you've specified your field name in the eval. The field name contains space (special character), so it should be enclosed within single quotes when using in the expressions of eval and where (for eval right side of the =). So if you change your eval like below, it would work just fine.

My Search
| eval Created_Time_timestamp=strptime('Created Time', "%A %B %e %Y %I:%M:%S %p %Z")
| table "Global Id", "Created Time", Created_Time_timestamp
0 Karma

erwanlebaron
Engager

The issue came from field "Created Time" with space...I add the rename of my field without space and it works

Thanks

0 Karma

TISKAR
Builder

Hi @erwanlebaron,
can you try this format: **%A %B %d %Y %I:%M:%S %p %Z**

| makeresults 
| eval time="Friday April 19 2019 5:23:48 PM CEST"
| eval x=strftime(strptime(time,"%A %B %d %Y %I:%M:%S %p %Z"),"%Y-%m-%d %H:%M:%S")
0 Karma

erwanlebaron
Engager

My day number is the format %e and not %d.
The issue came from the field name with the space.

Anyway thanks of having taken time to answer

0 Karma

vnravikumar
Champion

Hi

Check this

| makeresults 
| eval date="Friday April 19 2019 5:23:48 PM CEST,Friday February 8 2019 11:00:07 AM CET" 
| makemv delim="," date 
| mvexpand date 
| eval epoch =strptime(date,"%A %B %d %Y %I:%M:%S %p %Z")
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @erwanlebaron,
strptime command converts a string in epochtime, if you want it in a human readable format (e.g. mm/dd/yyyy HH:MM:SS) you have to use strptime and strtftime, something like this:

 [My Search]
| eval Created_Time_timestamp=strftime(strptime("Created Time", "%A %B %e %Y %I:%M:%S %p %Z"),"%m/%d/%Y %H:%M:%S")
| table "Global Id", "Created Time", Created_Time_timestamp

If it continues to dot run, rename fields with spaces and try again:

[My Search]
| rename "Created Time" AS Created_Time
| eval Created_Time_timestamp=strftime(strptime(Created_Time,"%A %B %e %Y %I:%M:%S %p %Z"),"%m/%d/%Y %H:%M:%S")
| table "Global Id" Created_Time Created_Time_timestamp

Ciao.
Giuseppe

erwanlebaron
Engager

The issue came from field "Created Time" with space...I add the rename of my field without space and it works

Thanks Giuseppe

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...