Splunk Search

Trouble with date conversion

mistydennis
Communicator

I need to create a field (30days) with a date 30 days from the date in a given field (pubdate). I believe I have that part working, but can't seem to get the date to convert to the format I want.

|makeresults
|eval pubdate="2022-09-30,2021-08-31"
|makemv delim="," pubdate
|mvexpand pubdate
|eval epochtime=strptime(pubdate, "%Y-%m-%d") 
|eval 30days=epochtime + 2592000
|convert ctime(30days)
|table pubdate, 30days


Which produces:

pubdate30days
2022-09-3010/30/2022 00:00:00.000000
2021-08-3109/30/2021 00:00:00.000000

 

All I want to do is to format the 30days date field the same was as pubdate - "%Y-%m-%d". Everything I'm trying is producing an error.

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The convert command has options to control the format of the time string, but I prefer to use strftime.

|makeresults
|eval pubdate="2022-09-30,2021-08-31"
|makemv delim="," pubdate
|mvexpand pubdate
|eval epochtime=strptime(pubdate, "%Y-%m-%d") 
|eval 30days=relative_time(epochtime, "+30d")
|eval 30days=strftime('30days', "%Y-%m-%d")
|table pubdate, 30days

I also used relative_time() to compute the new timestamp.

I didn't do it in my example, but try to avoid field names beginning with digits as they can confuse the parser.  For instance, the strftime call failed until I used single quotes around the first argument.

---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The convert command has options to control the format of the time string, but I prefer to use strftime.

|makeresults
|eval pubdate="2022-09-30,2021-08-31"
|makemv delim="," pubdate
|mvexpand pubdate
|eval epochtime=strptime(pubdate, "%Y-%m-%d") 
|eval 30days=relative_time(epochtime, "+30d")
|eval 30days=strftime('30days', "%Y-%m-%d")
|table pubdate, 30days

I also used relative_time() to compute the new timestamp.

I didn't do it in my example, but try to avoid field names beginning with digits as they can confuse the parser.  For instance, the strftime call failed until I used single quotes around the first argument.

---
If this reply helps you, Karma would be appreciated.
0 Karma

mistydennis
Communicator

Thank you, @richgalloway ! I see you have answered MANY questions regarding date and timestamps here - maybe it's time for you to just write a book and help us all out 🙂

0 Karma

isoutamo
SplunkTrust
SplunkTrust

In most cases I also prefer to use strftime, but in adhoc  queries and especially with mv fields, it’s easier way to convert all values.

0 Karma

isoutamo
SplunkTrust
SplunkTrust

Hi

have you tried 

...
| convert timeformat="%Y-%m-%d" ctimes(30days)

r. Ismo 

0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Calling All Security Pros: Ready to Race Through Boston?

Hey Splunkers, .conf25 is heading to Boston and we’re kicking things off with something bold, competitive, and ...

Beyond Detection: How Splunk and Cisco Integrated Security Platforms Transform ...

Financial services organizations face an impossible equation: maintain 99.9% uptime for mission-critical ...

Customer success is front and center at .conf25

Hi Splunkers, If you are not able to be at .conf25 in person, you can still learn about all the latest news ...