topic Re: Issue to find the format to convert string to date in Getting Data In

Issue to find the format to convert string to date

erwanlebaron — Wed, 30 Sep 2020 03:42:25 GMT

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

Re: Issue to find the format to convert string to date

gcusello — Tue, 21 Jan 2020 12:45:33 GMT

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

Re: Issue to find the format to convert string to date

vnravikumar — Tue, 21 Jan 2020 12:48:42 GMT

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")

Re: Issue to find the format to convert string to date

TISKAR — Tue, 21 Jan 2020 14:18:22 GMT

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")

Re: Issue to find the format to convert string to date

somesoni2 — Tue, 21 Jan 2020 19:06:54 GMT

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

Re: Issue to find the format to convert string to date

erwanlebaron — Wed, 22 Jan 2020 12:31:25 GMT

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

Thanks Giuseppe

Re: Issue to find the format to convert string to date

erwanlebaron — Wed, 22 Jan 2020 12:31:56 GMT

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

Thanks

Re: Issue to find the format to convert string to date

erwanlebaron — Wed, 22 Jan 2020 12:33:56 GMT

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